There are a few ways to establish relationships between tables:
- Using the Edit Relationships command located on the Design tab of the Ribbon
- Using the drag-and-drop method
Both methods give you the same end result, but the drag-and-drop method is much easier and saves you several steps.
To relate tables with the drag-and-drop method:
- Select a field name from one table by holding down the left mouse button.
- Drag the field name from one table to the other table in the desired relationship.
- Drop the first field name onto the field name you want to relate by releasing the left mouse button.
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.
- The Edit Relationships dialog box appears.
Edit Relationships Dialog Box
- Select the Enforce Referential Integrity option. This option is explained in detail below.
- Click Create.
Understanding types of relationships
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:
Enforcing referential integrity
In 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.
Editing existing relationships
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.