We had an MS Access guru at our company who left for another position. Before she left she gave me a quick introduction on how to create odbc from a sql server (we use odbc a lot at our company). I am really struggling with this and as I have no one to turn to at our company I was hoping you guys could help.

She imported the tables from odbc database and then machine datasource to fetch the tables. So far so good.

Then she added the relevant tables and created relationships between them to run queries, and here is where I am super confused. In some cases she created multiple relationships between two tables and sometimes she didn't! For example in table A she dragged warehouse_code to warehouse_code in table B. She then also dragged item_number from table A to table B. I am so confused about this and don't remember why she did this. I think she said something like because an item can exist at different warehouses.

Also worth mentioning that there are no IDs to match in the tables (at least no fields named item_id etc). Do you guys even know what I am talking about? Have searched everywhere for this concept, is it one to many relationship, many to many or what is this concept?

Hope you can help!

Thanks!

CodePudding user response：

Between two given Tables you can have one relationship involving two (of more) fields or two (or more) relationships each involving one field. Both cases are possible and have different implications.

The first case, as the first commenter pointed out, is typically used when you have a compound key in the master Table of the relationship.

The second case is typically used when you have two candidate keys in the master table, each of which is used as a master field in each of the two independent relationships.

In Ms-access the case of two independent relationships may be identified because it implies two table-boxes for the same table in the relationships pane.

More information in

So I not have some related tables here. I just feel like and want to, and need to join the two tables of data.

As such, we never cared or setup or "had" some relationship defined, but all we care about is creating and building a working query.

So, don't confuse the simple act of building some query with that of having setup a corrrect relatonships between tables.

For a working application? Yes, you most certainly will setup relatonships.

So, if you setup relatonships correctly, then you not be able to say add a customer "invoice" reocrd without FIRST having a customer record. You don't have to do this, but it is a very good idea for a working applicaton.

However, when dealing with imported data? You often may not have an pre-defined relationships.

Now, of course in "most" cases, a query that involves multiple tables will in near all cases "follow" what you defined as relationships in the relationships window but it not necessary a requirement at all.

As noted, when building a working application? Then yes, of course you want to setup the relatonships BEFORE you start adding data.

But for general data processing, and creating queries against say different tables of data you are slicing and dicing and working with?

You are free to cook up and draw lines between the tables in the query builder, and as such, often such quires will have zero to do with the relationships you defined, or in fact even when you don't have any relationships defined at all.

That above People and the list of hotels is a great example. I mean, it rather cool that I simple joined on both City and State, and did not have to write one line of data processing code for my desired results

(a list of people in cities that live in the same city as my hotel list).

So don't confuse what we call "referential integrity" and defined relationships. We define these relationships so it becomes impossible for you the developer to add a customer invoice without first having added the customer. And it also means that you, your code, or even a editing the tables directly will not allow this to occur.

However, when dealing with just reporting, or importing data to work on? Well, then often we will not have any relationships defined, but that sure does not stop us from firing up the query builder and drawing join lines between tables.