Create “Relationships”

Now that we have made all the tables, it is time to make the “Relationships” between these.

To make a “Relationship”, first close all tables.

If you do not know how to close a table, that would surprise me, just click the X in the upper right corner of the table.

When all tables are closed, choose the contextual tab “Database Tools” in the “Ribbon” and click the “Relationships” button.

The “Relationships” tab opens in the window.

If it is the first time you’re creating a “Relationship” in this “Database”, a “Show Table” dialog appears:

You have the ability to establish “Relationships” between “Tables”, “Queries” and both.
You’ll see these tabs at the top of the “Show Table” dialog box.

Because we do not have queries as yet, I’ll go with the “Tables” tab.

In the “Show Table” under the “Tables” tab, we see the three tables which we have already made in our “Database”.

Because we need to connect these with each other, we must put them under the “Relationships” tab.

To do this, select the “Tables” tab in the dialog box and click the “Add” button.

The faster way is by double-clicking on a table in the dialog.

We repeat this for each table and click the close button when we’re done.

Now all our tables are shown in the “Relationships” tab .
If your tables are not placed together, which is rarely the case, you can move them by clicking the title bar of the table, and dragging to the right place.

When all fields from a table cannot be seen, place your mouse pointer over the edge of the table and when it changes into a double arrow, click and drag it to the desired size.

To connect the tables, click and drag the “AlbumID” from the “Album” table, onto the “AlbumID” field of the “Song” (1) table.

This opens the “Edit Relationships” window.

In this window you can see that a “one-to-many” “Relationshipship” is included, which is what you are creating.
The “AlbumID” from the “Album” table connects to  the “AlbumID” from the “Song” table.

And we have the “Join Type …” button.
This button is useful when you create a “Relationship” between two tables that already have data.

When we click it opens a dialog with three options:

1. Only include rows where the joined fields from both tables are equal.
2. Include ALL records from ” Album” and only those records from “Song” where the joined fields are equal.
3. Include ALL records from “Song” and only those records from “Album” where the joined fields are equal.

Here is some explaination:
The first option is comes down to this:
“Show only the Albums with songs.”
The second option corresponds to:
“Show All albums, including those that have no songs.”
The third option we could interpret as:
“Show All the songs, including those for which no there is no album number.”

The “Album” table is already entered with several records, but the “Song” table has no records. So the second option seems the best choice, so click OK.

As the last item in the “Edit Relationships” window, we have the “Referential integrity”. This is quite important.

What is “Referential Integrity”?
An example:
You have a one-to-many “Relationship” between the “Album” table and the “Song” table and you want to delete an album from the “Album” table.

If the album that you want to delete has songs in the “Songs” table, these songs will be “unconnected songs.” Follow me?

The songs still have a AlbumID in the “Songs” table but, this AlbumID no longer exists because, you have just removed it from the “Album” table.

This is why we have the “Referential Integrity” checkbox.
“Referential integrity” is intended to prevent unconnected records.
So when this box is checked, “Access” will refuse any operation that would violate the table “Relationship”. For example, removing a AlbumID in the “Album” table.

When the “Enforce Referential Integrity” checkbox is selected, we have the opportunity to select the two underlying boxes. The first, “Cascade Update Related Fields” where the primary field from one table changes related fields in the other table automatically . Example: you change “AlbumID” field in the “Album” table, then the “AlbumID” field in the “Song” table changes automatically.

When the “Cascade Delete Related Records” check box is selected, when we delete records from the “Album” table, it would delete the related records in the “Song” table automatically. Normally this would not have been possible because the “Enforce Referential Integrity” box was checked.

For this “Relationship”, we select all three checkboxes and click OK.

Do the same for the “SingerID” field in the “Lead Singers” table. Drag the “SingerID” field to the “Song” table. Only that for the “Join properties”, choose “to include only rows where the joined fields from both tables are equal”, which is the default setting.

Then we have this:

You can always change the settings of the “Relationship” by double-clicking the line between the two tables, or by selecting a line, and clicking on the “Edit Relationships” button in the “Ribbon”.

To remove a “Relationship”, select the line of the “Relationship” and click the “Delete” button on your keyboard. Alternatively, you can right-click a line and select “Delete” in the drop-down menu.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats