Creating Database Relationships in Access

Use the Relationships GUI tool to set common relationships among tables

Graphic of red pencil outlining database relationships

 alexsl / Getty Images

By
Mike Chapple
Writer
Former Lifewire writer Mike Chapple is an IT professional with more than 10 years' experience cybersecurity and extensive knowledge of SQL and database management.
our editorial process
Mike Chapple
Updated January 06, 2020

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.

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

All currently supported versions of Microsoft Access offer the Relationships window, including Access 2019, 2016, Access for Office 365 and Access for Mac.

How to Make an Access Relationship

  1. With Access open, go into the Database Tools menu at the top of the program. From within the Relationships area, select Relationships.

    access database tools menu

  2. 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 select Add.

    If the database already features mapped relationships—usually because of existing forms, reports, or queries—then Access bypasses this pop-up and instead move straight to the Design view of the Relationships window.

    access show table screen

  3. Drag a field from one table to the other table so that the Design window opens. If your database already infers relationships, this window will already populate with relationships.

    Hold down the Ctrl key to select multiple fields; drag one of them to drag all of them over to the other table.

    database relationships

  4. Choose any other options you want, like Enforce Referential Integrity or Cascade Update Related Fields, and then select Create or Create New.

Selecting enforce referential integrity means that the database will not accept data that doesn't match the relationship. The two cascade options forces the database to purge or update when the source record changes. For example, selecting cascade update related fields will prompt the database to correct the value in the related table when a value in the source table changes; if it's left unchecked, the old values remain, and new records earn the new value.

edit relationships in access

Join Types

Access supports three types of joins through this wizard—one-to-one, one-to-many, and many-to-one. In general, you'll typically use the first join type, which links the data when the records in one match the records in the other.

Access supports other kinds of joins, but you'll have to manage those through advanced tools, not through the Relationships window.