Use relationships

Create many-to-many relationships

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

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 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 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 a relationship

Delete a relationship

Excel training

Outlook training

Use a many-to-many relationship between tables when a single item in table A can be related to many items in table B, and vice versa. For example, a product can appear in many orders, and an order can contain many products.

In this demo, we'll expand our database into a basic order tracking system with tables for products and orders.

We’ll learn about the junction table and composite key, and use them with our other tables to create the many-to-many relationship.

The first step in building a many-to-many relationship is to create a new table.

This is called the “intermediate table,” and we’ll use it to store the data from the other tables involved in the relationship.

The intermediate table is also referred to as the “linking” or “junction” table.

The next thing you do is create a pair of one-to-many relationships between the junction table and the other tables involved in the relationship.

As part of that, you need to identify both of these fields as the junction table’s primary key. This becomes the “composite key.”

We build the junction table in Design View, so we select Create, Table Design. Here's our new, blank table.

Now let’s open the Products and Orders tables.

Let’s look at the primary keys for both tables. The Products table uses an AutoNumber field, called Product ID.

The primary key for the Orders table is called Order ID, and it's also an AutoNumber field.

Now we return to our junction table, where we enter Product ID, select Number, enter Order ID, and then select Number again.

Now, let’s select both fields. To do this, position the pointer in the grey box next to either field and drag.

With both fields selected, on the ribbon, select Primary Key. This creates the composite key for the junction table.

Having both fields in the composite key ensures that each order and everything IN each order are uniquely identified.

Press Control S to save the junction table and call it Order Details.

Now we can create the relationships. Start by closing all your open tables. Select Database Tools and then select Relationships.

On the Design tab, select Show Table. Add the Order Details, Orders, and Products tables. Now close the dialog box.

Arrange the tables so Order Details—the junction table—is in the middle. Drag the Order ID field from the Orders table over to the junction table.

In the Edit Relationships dialog box, select Enforce Referential Integrity, and then select Create to create the relationship.

Do the same with the Product ID field. Select Enforce Referential Integrity, select Create, and that's it.

Press Control S to save your relationship diagram layout changes.

You've now used a junction table and composite key with your existing tables to create a many-to-many relationship in your Access database.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×