More about Relationships

I am having a tough time with making relationships (I think I understand the concept but actually translating this into action is not happening).

Let me offer a way to relate tables..

1. Open your relationship window and, one by one, delete all the existing relationships. Then hide, one by one, all the tables. I suggest this solely to eliminate any possible phantom tables that may have appeared.
2. Now, right click to reveal the dialog box that will allow you to see all five of your tables in the relationship window. You should see tables for Authors, Books, BookAuthor (the linking table), Publishers, and Orders
3. Then check each of the foreign keys in each of the linked tables to ensure that they are all data type number, long integer, duplicates OK. You should have FKs pointing to PKs in these tables

• BookAuthor FK pointing to Book PK
• BookAuthor FK pointing to Author PK
• Book FK pointing to Publisher PK
• Orders FK pointing to Book PK

4. Then use the Lookup Wizard on the data type for each of your FKs to have the wizard look up values in the PK table. The lookup tool will create a query that will use the actual PK as a link to the FK, but can permit you to see the other fields that are connected to each PK. Your lookups should be like this:

• BookAuthor FK pointing to Book PK with lookup showing book title field sorted in alphabetical order of book titles
• BookAuthor FK pointing to Author PK with lookup showing author name field sorted in alphabetical order of author names
• Book FK pointing to Publisher PK is slightly different. You know that some of the records in the publisher table include publishers with the same name in two locations (thus you can have two records for a publisher that has the same name). But if a publisher is listed twice with the same name, it will be because one name is associated with a location in one country or state and the second name is associated with a country or state in a second location. Accordingly, the lookup will should show you the publisher name as well as the publisher country and the publisher state. You wil then have the lookup table sort the three fields first by name, then by country, then by state, all in alphabetical order. In this fashion, you will be able to see the difference between Publisher A in Country 1 and Publisher A in Country 2 when you select the publisher to relate the book to.
• Orders FK pointing to Book PK with lookup showing book title field sorted in alphabetical order of book titles

The activation of the lookup wizard will create underlying relationships between the FK and the PK. The wizard will put a name on the relationship, but this is only a background name for the query that the lookup wizard creates and is not imporant. Accept what the wizard wants to name the relationship.

5. Now, with lookup tables on each of your FKs, go back to the relationship view. The lookup wizard may already have created the FK to PK relationships for you. If it hasn't, select one FK that is not currently showing a relationship, then right click to reveal a dialog box. In the dialog box, select "show direct". That action should create a relationship line between the FK with the lookup table on it to the PK that it is related to.

6. Then, edit all those relationship so that you enforce referential integrity on each of them The result should be one to many relationships between

• BookAuthor FK (many) pointing to Book PK (1)
  One Book may be related to many links to Authors, but each link will be related to one and only one book
• BookAuthor FK (many) pointing to Author PK (1)
  One Author may be related to many links to Books, but each link will be related to one and only one author
  
• Book FK (many) pointing to Publisher PK (1)
  One Publisher may be related to many Books, but each book will be related to one and only one publisher
• Orders FK (many) pointing to Book PK (1)
  One Book may be related to many Orders, but each order will be related to one and only one book

See if this helps.