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

Contribute!

Did you have an idea for improving this content? We’d love your input.

Improve this pageLearn More