If you want to build a table relationship in Access 2016, you must first open the Relationships window. You do that by following these steps:
-
Click the Database Tools tab on the Ribbon.
The Relationships group appears on the Ribbon.
-
From the Relationships group, click the Relationships button.
The Relationships window appears along with the Show Table dialog box.
-
For each pair of tables you want in the relationship, click the table and then click Add.
In the big Relationships workspace, a little window lists the fields in the selected table. As you add tables to the layout, a separate window appears for each table. You can see these windows to the left of the Show Table dialog box.
Repeat Step 3 for each pair of tables you want to relate. If one of the tables in the pair is already present (due to an existing relationship it has with another table), you don't have to add it again.
-
After you finish adding tables, click the Close button.
Decide which tables you want to relate. Since the one-to-many relationship is the most common, these instructions pertain to it.
-
Put the mouse pointer on the field you want to relate in the parent table (the "one" table in the one-to-many relationship) and hold down the left mouse button.
Usually the field you want to relate in the parent table is the primary key.
-
While holding down the left mouse button, drag the mouse pointer from the parent field to the child field in the child table (the "many" table in the one-to-many relationship).
The fields typically have the same name, such as LocationID.
-
Point to the related field in the child table and release the mouse button.
The Edit Relationships dialog box appears, detailing the soon-to-be relationship.
-
In the Edit Relationships dialog box, select the Enforce Referential Integrity and Cascade Delete Related Records options.
This ensures that when you delete a record from the parent table, Access deletes all related records in the child table.
-
Click Create to set the relationship.
The Edit Relationships dialog box closes and a join line appears connecting the two fields, indicating that the fields are related.
