How to Create Database Relationships in Access

Software

by

Updated September 30, 2018

One of the major advantages of databases like Microsoft Access is their ability to maintain relationships between different data tables. The power of a database makes it possible to correlate data in many ways and ensure the consistency (or referential integrity) of this data from table to table.

Imagine a small database created for the "Simple Business" company. We want to track both our employees and our customer orders. We might use a table structure to do this, where each order is associated with a specific employee. This information overlap presents the perfect situation for the use of a database relationship.

Together, you could create a relationship that instructs the database that the Employee column in the Orders table corresponds to the Employee column in the Employees table. When a relationship is formed between two different tables, it becomes easy to combine that data together.

Let's take a look at the process of creating a simple relationship using a Microsoft Access database:

How to Make an Access Relationship

With Access open, go into the Database Tools menu at the top of the program.
From within the Relationships area, click or tap Relationships.

The Show Table window should appear. If it doesn't, choose Show Table from the Design tab.
From the Show Table screen, choose the tables that should be involved in the relationship, and then click/tap Add.
You can now close the Show Tables window.
Drag a field from one table to the other table so that the Edit Relationships window opens.

You can hold down the Ctrl key to select multiple fields; drag one of them to drag all of them over to the other table.
Choose any other options you want, like Enforce Referential Integrity or Cascade Update Related Fields, and then click or tap Create.