Creating Database Relationships in Access

Use the Relationships GUI tool to set common relationships among tables

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 on February 21, 2021

What to Know

  • In Database Tools, go to Relationships, select tables, drag a field from one table to the other, and click Create.
  • Access supports three types of joins through this wizard: one-to-one, one-to-many, and many-to-one.

This article explains how to create a simple relationship using Access for Microsoft 365, Access 2019, Access 2016, 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 you want to 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 goes 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, such as 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 force 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.

Was this page helpful?