Home and Learn: Microsoft Access Course


Access One-to-Many Relationships

What we'll do is to link our authors to the books they wrote. We're going to need a new field in our Books table. This new field will be the Primary Key from the Authors table. However, because it's in the Books table, the AuthorID field will be the Foreign Key.

If it's not already open, double-click your tblBooks table to open it up. It should open in Datasheet View. Where it says Click to Add, add another field. For the Data Type, set it to Number. Change the field name from the default Field1 to AuthorsID, to match the field name in the Authors table. (Primary Keys and Foreign Keys don't have to have the same name, or even similar names, but it helps if they do. It's easier to see the relationship that way. Ours are slightly different, AuthorID and AuthorsId, one singular the other plural. You can call them both AuthorID, if you want. Access doesn't care.)

An Access table with a second ID field added

Access doesn't care in what order your columns are in, and you can move them around. It helps to keep the ID fields together. To move a column, hold down your left mouse button on the column you want to move. Hold it down on where it says AuthorsID. Keep your left mouse button held down and drag to the left. Let go when you see a black line to the right of ID. The moved column will then look like this:

An Access table column moved to the left

Now to set up a relationship between the Authors and their books. Close down any tabs in the main area. From the Ribbons at the top of Access, locate the Database Tools Ribbon. On the Relationships panel, click on Relationships:

The Database Tools Ribbon in Access with the Relationships item highlighted

When you do, you should see a new tab appear in the main area. Because you already created a relationship when you added the Genres table as a dropdown box in the Books table, you should see this:

The Relationship diagram in Access with two tables displayed

Notice the line coming from the ID field of the Genres table to the Genre field in the Books table. That tells you that there is some sort of relationship between the two tables.

But we want to add some tables to this screen so that we can create a new relationship. To do that, notice the Ribbon has changed at the top of Access. You should be on the Design Ribbon. Locate the Add Tables item on the Relationships panel:

The Access Design ribbon with the Add Tables item highlighted

When you click on Add Tables, you should see a dialog box appear with your tables on it:

The Show Tables dialog box with two tables selected

Hold down a SHIFT key on your keyboard and click to select both the tblAuthors and the tblBooks tables. Then click the Add button at the bottom. The Show Tables dialog box won't close by itself, so just click the Close button.

The tables you selected should now appear on the Relationship tab. It should look something like this:

The Access relationship diagram with three tables displayed

You can drag the tables around on this tab, so things look a lot neater. Hold your left mouse button down on a table title (where it says tblAuthors in the image above, for example). Keep your left mouse button down and drag to a new location. In the image below, we've dragged the tables so that the tblAuthors is on the left, tblBooks is in the middle and tblGenres is on the right.

The Access relationship diagram with three tables rearranged

What we'd like to do is to link the AuthorID field in tblAuthors to the AuthorsID field in tblBooks. The first one is the Primary Key and the second one the Foreign Key we've just created.

So, select the AuthorID field under tblAuthors. Click on it with your left mouse button. Keep the left mouse button held down and drag onto the AuthorsID field in tblBooks:

Arrows showing where to drag one field onto another to create a relationship

Let go when your see your mouse pointer change shape. You should then see a dialog box appear. This one:

The Edit Relationships dialog box in Access

You get two dropdown lists here. The one on the left is so you can select the Primary Key table. The dropdown on the right is so you can select your Foreign Key table. Because you dragged and dropped a field from tblAuthors onto a field in tblBooks, the table under Table/Query at the top is tblAuthors, which you want as the Primary Key. Under Related Table/Query, it says tblBooks, which is the Foreign Key.

Notice at the bottom it says Relationship Type: One-To-Many (one author can have many books). The One is the table on the left, tblAuthors. The table on the right is the many, which is tblBooks. If you want something else, click the dropdowns to see a list of your tables:

The Edit Relationships dialog box showing a Related Table selected

Under the tables is a checkbox that says Enforce Referential Integrity. Checking this box ensures that you can't delete an Author from the Primary Key table and leave the author's books in the Foreign Key table. You can't have orphan books, in other words, books with no author. So check this box to leave your Edit Relationship dialog box looking like this:

The Enforce Referential Integrity option in the Edit Relationships dialog box

Click the Create button top right to create the relationship. Your Relationship tab will then look like this:

The Relationships diagram in Access showing a Primary Key linked to a Foreign Key

Notice the thick black line linking the two AuthorID fields. There's a little number 1 at the top and an infinity symbol at the bottom. This tells you that you have a one-to-many relationship on these two fields. The little key symbols to the left of the table fields tells you where your Primary Keys are.

Let's see what setting up a relationship does. (You can close down the Relationship tab, now. Click the Close icon in the Ribbon at the top of Access. If you want to open up the tab again, you do it from the Database Tools Ribbon. Click the Relationships item on the Relationships panel.)

Finally, after all that hard work, we can enter a record in our database. We'll do that in the next lesson below.

<--Back to the Microsoft ACCESS Contents Page


Email us: enquiry at homeandlearn.co.uk