Define relationships between tables

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

1. Close any tables you have open. You can't create or modify relationships between open tables.

2. If you haven't already done so, switch to the Database window. You can press F11 to switch to the Database window from any other window.

3. Click Relationships aa170906(v=office.10).md on the toolbar.

4. If your database doesn't have any relationships defined, the Show Table dialog box will automatically be displayed. If you need to add the tables you want to relate and the Show Table dialog box isn't displayed, click Show Table aa209391(v=office.10).md on the toolbar. If the tables you want to relate are already displayed, skip to step 6.

5. Double-click the names of the tables you want to relate, and then close the Show Table dialog box.

6. Drag the field that you want to relate from one table to the related field in the other table.

   To drag multiple fields, press the CTRL key and click each field before dragging them.

   In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key in the other table. The related fields don't have to have the same names, but they must have the same data type (with two exceptions) and contain the same kind of information. In addition, when the matching fields are Number fields, they must have the same FieldSize property setting. The two exceptions to matching data types are that you can match an AutoNumber field with a Number field whose FieldSize property is set to Long Integer; and you can match an AutoNumber field with a Number field if both fields have their FieldSize property set to Replication ID.

7. The Edit Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary.

   Set the relationship options if necessary. For information about a specific item in the Relationships dialog box, click the question mark button , and then click the item.

8. Click the Create button to create the relationship.

9. Repeat steps 5 through 8 for each pair of tables you want to relate.

   When you close the Relationships window, Microsoft Access asks if you want to save the layout. Whether you save the layout or not, the relationships you create are saved in the database.

Notes  

• If you need to view all the relationships defined in the database, click Show All Relationships aa170916(v=office.10).md on the toolbar. To view only the relationships defined for a particular table, click the table, and then click Show Direct Relationships aa170918(v=office.10).md on the toolbar.

• If you need to make a change to the design of a table, you can right-click the table you want to change, and then click Table Design.

• You can create relationships using queries as well as tables. However, referential integrity isn't enforced with queries.

• To create a relationship between a table and itself, add that table twice. This is useful in situations where you need to perform a lookup within the same table. For example, in the Employees table in the Northwind sample database, a relationship has been defined between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

• You can also define a relationship by using the keyboard.