| 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 11.1). If it doesn't appear, choose Relationships, Show Table . Figure 11.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. Tip 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 11.2. Each table box lists all the fields in that table. Tip 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.
Figure 11.2. Tables in the Relationships window.  -
After you have the tables available in the relationships window, you can create the relationships between the table. 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 11.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 11.3). Caution 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; AutoNumber fields are considered long-integer number fields.
Figure 11.3. The Edit Relationships dialog box asks you to define the relationship you're creating.  -
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 11.4). Figure 11.4. The join line represents a relationship between the two fields.   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). |
