Use relationships

Create many-to-many relationships

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Try it!

What’s a many-to-many relationship?

Many-to-many relationships are the most commonly used table relationships. They provide crucial information, such as which customers your salespeople have contacted and which products are in customer orders.

A many-to-many relationship exists when one or more items in one table can have a relationship to one or more items in another table. For example:

  • Your Order table contains orders placed by multiple customers (who are listed in the Customers table), and a customer may place more than one order.

  • Your Products table contains the individual products you sell, which are part of many orders in the Order table.

  • One order may include one instance (or more than one instance) of a specific product and/or one instance (or more than one instance) of multiple products.

For example, customer Elizabeth Andersen’s order number 1012 might contain one each of products 12 and 15 and five of product 30.

Create many-to-many relationships

You create many-to-many relationships differently than you do one-to-one or one-to-many. For those relationships, you simply connect the appropriate fields with a line. To create many-to-many relationships, you need to create a new table to connect the other two. This new table is called an intermediate table (or sometimes a linking or junction table).

In the scenario described earlier, you create an Order Details table with records that contain, for each item in any given order, the ID from the Order table and the ID from the Products table. You create a primary key for that table using the combined keys from the two tables.

In our scenario, Elizabeth Andersen’s order number 1012 consists of products 12, 15, and 30. That means our Order Details entries look like this:

Order ID

Product ID

1012

12

1012

15

1012

30

Elizabeth ordered one each of products 12 and 15, and five of product 30. We can’t add another row with 1012 and 30 because the Order ID and the Product ID combined make up our primary key, and primary keys must be unique. Instead, we add a Quantity field to the Order Details table.

Order ID

Product ID

Quantity

1012

12

1

1012

15

1

1012

30

5

Create an intermediate table

  1. Select Create > Table.

  2. Select Save Save.

  3. For Table Name, enter a descriptive title. To indicate its purpose, you might want to include junction or intermediate in the table name.

Create fields in the intermediate table

As the first table column, Access automatically adds an ID field. Change that field to match the ID of the first table in your many-to-many relationship. For example, if the first table is an Orders table called Order ID, and its primary key is a number, change the name of the ID field in the new table to Order ID and, for the data type, use Number.

  1. In Datasheet View, select the ID column heading and then type the new name for the field.

  2. Select the field you just renamed.

  3. On the Table Fields tab, under Data type, select a data type to match the field in the original table, such as Number or Short Text.

  4. Select Click to Add, and then select a data type that matches the primary key in the second table. In the column heading, which is already selected, type the name of the primary key field from the second table, such as Product ID.

  5. If you need to track any other information about these records, such as item quantity, create additional fields.

Combine primary ID fields

Now that you have fields containing IDs from the two tables you want to connect, create a primary key, based on those IDs, in the intermediate table.

  1. In Design View, open the intermediate table.

  2. Select both rows that contain the IDs. (If you followed the earlier procedure, these are the first two rows.)

  3. Select Table Design > Primary Key. Key icons appear next to both ID fields.

    Primary Key in table screenshot

Connect the three tables to create the many-to-many relationship

To complete the many-to-many relationship, create a one-to-many relationship between the primary key field in each table and the matching field in the intermediate table. For details on how to do this, see Get started with table relationships.

When you finish, the relationships should look something like this:

Screenshot of the connections between three database tables

Want more?

Get started with table relationships

Create, edit or delete a relationship

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.