To create a relationship between tables, open the Relationships window. Before you can create relationships between tables, you must first add the tables to the Relationships window. Follow these steps:
In the database, select Tools, Relationships , or click the Relationships button on the toolbar to open the Relationships window.
If you haven't selected any tables yet, the Show Table dialog box appears automatically (see Figure 9.1). If it doesn't appear, choose Relationships, Show Table .
Figure 9.1. Add tables to your Relationships window with the Show Table dialog box.
Click a table that you want to include in the Relationships window, and then click the Add button.
| || |
Well-Designed Databases and Relationships In a well-designed database, every table in the database is related to at least one other table in the database. So, you might want to add all your tables to the Relationships window.
Repeat step 3 to select all the tables you require in the Relationships window, and then click Close . Each table appears in its own box in the Relationships window, as shown in Figure 9.2. Each table box lists all the fields in that table.
Figure 9.2. Tables in the Relationships window.
| || |
Enlarge the Table Box If you can't see all the fields in a table's box, drag the table border to make it large enough to see all the fields.
After you have the tables available in the Relationships window, you can create the relationships you want to exist between them. Remember that you must link the tables using a common field. For example, you can link the Customers table to the Orders table using the Customer ID field, as shown in Figure 9.2. Select the common field in the table where it is the primary key (in this case, the Customer table). Drag the field and drop it on its counterpart (the same field name ) in the other table (in this case, Orders). The Edit Relationships window opens (see Figure 9.3).
Figure 9.3. The Edit Relationships dialog box asks you to define the relationship you're creating.
| || |
Field Type Matters The fields to be linked must be of the same data type (date, number, text, and so on). The only exception is that you can link a field with an AutoNumber format to another field with a number format.
The Edit Relationships dialog box shows the fields that will be related. It also allows you to enforce referential integrity, which you learn about in the next section. For now, click Create . A relationship is created, and you'll see a join line between the two fields in the Relationships window (see Figure 9.4).
Figure 9.4. The join line represents a relationship between the two tables.
When you create relationships between tables, it's important that you save them. Click the Save button on the Relationships toolbar to save the current relationships (and the list of tables available in the Relationships window).