How to Create a Relationship in Access

Posted on by Ian

To create a relationship in Access 2013 or 2016:

  1. While viewing a table in Design view, and ensuring that the DESIGN tab is selected, click Relationships from the Ribbon
  2. A Show Table dialog will appear with a list of tables and queries. Select the tables (and/or queries) that you wish to create a relationship between, and close the dialog
  3. The Relationships tab will appear with the selected tables. Click and drag a field on top of a field in another table to initiate a relationship.
  4. The Edit Relationships dialog will pop up containing both fields. Make any adjustments and click OK

This will create the relationship. You will see the tables in the Relationship tab with lines depicting the relationship between the tables.

Here are screenshots for the above steps.

The “Relationships” Button

While viewing a table in Design view, and ensuring that the DESIGN tab is selected, click Relationships from the Ribbon.

Screenshot of the Relationships button on the Ribbon in MS Access 2013.
The “Relationships” button on the Ribbon.

The “Show Table” Dialog

A Show Table dialog will appear with a list of tables and queries. Select the tables (and/or queries) that you wish to create a relationship between, and close the dialog.

Screenshot of the Show Tables dialog while creating a relationship in Access 2013
The “Show Table” dialog allows you to select which tables to establish the relationship between.

The “Relationships” Tab

The Relationships tab will appear with the selected tables. Click and drag a field on top of a field in another table to initiate a relationship.

Screenshot of the Relationships tab in MS Access 2013
The “Relationships” tab allows you to establish the relationship by clicking and dragging a field over a field on another table. By dragging the Customers.CustomerId field over Orders.CustomerId, a relationship is established between the two tables, with Customers.CustomerId as the primary key and Orders.CustomerId as the foreign key. The same could be done for the two ProductId fields.

The “Edit Relationships” Dialog

The Edit Relationships dialog will pop up containing both fields. Make any adjustments and click OK

Screenshot of the Edit Relationships dialog in Access 2013.
The “Edit Dialog” allows you to make adjustments to the relationship, such as specifying that it must enforce data integrity and more.

The Result

Screenshot of a many-to-many relationship in the Relationships tab.
The “Relationships” tab showing a many-to-many relationship. In this case, the two CustomerId fields have been linked and the two ProductId fields have been linked. The Customers.CustomerId and Products.ProductId are primary keys, and Orders.CustomerId and Orders.ProductId are foreign keys.