Create many-to-many relationships

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:

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.

Create an intermediate table

1. Select Create > Table.

2. Select 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.

   

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: