Types of Table Relationships

Learning Outcomes

  • Define one-to-one relationships
  • Define one-to-many relationships
  • Define many-to-many relationships

There are three types of relationships in Access between tables:

  • One-to-one
  • One-to-many
  • Many-to-many

Screenshot of Microsoft Access showing one-to-one, one-to-many, and many-to-many relationships between tables.

One-to-One Relationships

A one-to-one relationship means that a record in the first table can have one and only one record related to it in a second table. For example, a product listed in a products table would have only one country of origin record in a operations table.

Practice Question

One-to-Many Relationships

One of the most common relationships between tables is the one-to-many relationship. In a one-to-many relationship, a record in the first table is related to multiple records in the second table. For example, an employee table could have relationships with a payroll table that contains multiple records of paychecks.

Practice Question

Many-to-Many Relationships

Many-to-many relationships are defined as a record in the first table having multiple relationships in the second table and those records in the second table also having multiple relationships within the first table. For example, a streaming service would have TV series that are selected to view by multiple customers while each customer of the service would watch multiple TV series.

Practice Question