Defining Table Relationships

You can define table relationships in several ways. When you first create tables in your database using the Table Wizard, the wizard gives you an opportunity to define table relationships. You can also define relationships in the Database window or in Design view. This method gives you more control over your table relationships and also gives you a quick snapshot of all the relationships in your database. After you define a relationship, you can double-click the connection line to modify or add to the relationship.

Define Table Relationships

In the Database window, click the Relationships button on the Database toolbar.

If relationships are already established in your database, they appear in the Relationships window. In this window you can create additional table relationships.

If necessary, click the Show Table button on the Relationship toolbar to display the Show Table dialog box.

Click the Tables tab.

Click the table you want.

Click Add.

The table or query you selected appears in the Relationships window.

Repeat steps 4 and 5 for each table you want to use in a relationship.

Click Close.

Drag the common field in the first table to the common field in the second table. When you release the mouse button, a line appears between the two tables, signifying that they are related . Also, the Edit Relationships dialog box opens, in which you can confirm or modify the relationship.

Click the Join Type button if you want to specify the join type. Click OK to return to the Edit Relationships dialog box.

Click Create to create the relationship.

Did You Know?

You can view the relationships you want to see . Click the Show Direct Relationships button on the Relationship toolbar to see tables that are directly related to each other. Click the Show All Relationships button on the Relationship toolbar to see all the relationships between all the tables and queries in your database.

You can print the Relationships window . Open the Relationships window you want to print, click the File menu, click Print Relationships, select the print settings you want, and then click OK.

You can delete a table relationship . In the Relationships window, select the line that joins the tables that you no longer want related to one another. Click the Edit menu, and then click Delete. In the message box, click Yes to confirm that you want to permanently delete this relationship. You will not be able to undo this change.

Join Types

Join Types	Description
Include rows only where the joined fields from both tables are equal	Choose this option if you want to see one record in the second table for every record that appears in the first table. The number of records you see in the two tables will be the same.
Include ALL records from "xxx" (the first table) and only those records from "yyy" (the second table) where the joined fields are equal	Choose this option if you want to see all the records in the first table (even if there is no corresponding record in the second table) as well as the records from the second table in which the joined fields are the same in both tables. The number of records you see in the first table might be greater than the number of records in the second table.
Include ALL records from "yyy" (the second table) and only those records from the "xxx" (the first table) where the joined fields are equal	Choose this option if you want to see all the records in the second table (even if there is no corresponding record in the first table) as well as the records from the first table in which the joined fields are the same in both tables. The number of records you see in the second table might be greater than the number of records in the first table.