By now, you've set up the tables you need for your database and have created fields for the different tables. Relationships provide Access 2007 with the means to bring this information together for you when you need it.
This lesson explains how to establish relationships between the tables in an Access 2007 database. You will learn how to read and manipulate the relationship map. You will also learn about primary and foreign keys, relationship types, and referential integrity.
Download the example to work along with the video.
The relationship map lists all of the tables that were selected to relate, as well as all of the fields that were previously set up for that table. Notice that the first field has a key icon next to it. This is the primary key for the table.
Primary KeysA primary key is the first field in each table of the database. You may recall that this field auto-numbers by default, so every record in the table has its own unique number to identify it. Access uses this number to quickly pull information together when you run queries or reports, which are covered later.
In the example above, the primary key for the Customers table is Customer ID, the primary key for the Orders table is Order ID, and the primary key for the Books table is Book ID.
A foreign key is a field that is the primary field in its own table but that shows up in another table. If you look closely at the Orders table, the fields Customer ID and Book ID appear there, as well as in their own respective tables. These fields are the primary key in their own tables, but in the Orders table, they are considered foreign keys.
Foreign KeysThere are a few ways to establish relationships between tables:
Both methods give you the same end result, but the drag-and-drop method is much easier and saves you several steps.
In the example above, we selected the Book ID field from the Books table and dragged and dropped it on the Book ID field in the Orders table.Edit Relationships Dialog Box
Access 2007 allows for several different types of relationships. These include:
The relationship type you will come across most frequently—and the one created in our bookstore scenario—is the One-to-Many relationship.
The One-to-Many relationship means data for that field will show up a single time in one table but many times in the related table.
For example, let's look at one of the book titles in our bookstore. The Book ID for the book should appear only once in the Books table because this table lists every title stocked in the store. But it will probably appear many times in the Orders table because we hope it gets ordered by many people many times.
The symbols for the One-to-Many relationship look like this:
One to Many RelationshipsIn the Edit Relationships dialog box, an option to Enforce Referential Integrity appears.
You should click Enforce Referential Integrity to make sure you never have an order for a book that doesn't appear in the Books table. Selecting this option tells Access to check for these things when someone is working with your data records.
Access 2007 allows you to edit relationships that already exist. This can be done using the Edit Relationships command on the Ribbon. However, a much simpler way is to simply double-click the link that appears in the relationship map. Either method brings up the Edit Relationships dialog box, where you can change your settings.
If you haven't already done so, save the sample Ready2Read database to your computer.
