many-to-many relationship in Access learning

Create a many-to-many relationship in Access

A many-to-many relationships in a relational database exists when one record in table A can be referenced by one or more records in table B and one record in table B can be referenced by one or more records in table A. In this tutorial you will learn how to create a many-to-many relationship in Access 2010.

A many-to-many relationship consists of three tables. Below is a schematic view of the many-to-many relationship.

Schematic representation of a many-to-many relationship.The table in the middle is called a

Create a many-to-many relationship in Access

I will illustrate the creation of a many-to-many relationship with an example, including a Product table, an Order table and a junction table called Order2Product. To create a many-to-many relationship in Access follow these steps.

Create the two tables you want to link in a many-to-many relationship

In this example I created the Order table and the Product table below. Both tables have anAutonumber primary key field. These primary key fields will later be used to create a many-to-many relationship between these tables. If you want to know how to create a table in design view, have a look at this article: how to create a table in Access 2010.

The order table

The product table

Creating the junction table

In this case there are two ways of linked products to orders. We can create a junction table with ansurrogate primary key field, like the ID Autonumber field Access automatically provides.

We can also create our own composite primary key that consists of the two foreign key field in the junction table. I will show you both ways and discuss the implications of each solution.

Create the junction table using a surrogate key

The junction table with surrogate primary key will have three fields, including the ID primary key field that Access automatically creates for you. The two non-primary key fields are foreign keys. They will reference the primary keys of the Order and Product tables (or perhaps some other tables in your case).

1. Create a new table and name it something like LeftTable2RightTable. In my example I named it Order2Product. This notation is often chosen for junction tables.
2. Go to the design view of your new table (Read how to create a table in Access 2010 if you need more info on creating tables in design view).
3. Leave the automatically supplied ID primary key field in tact. Add two new fields. In my example I created order and product fields. The order field will reference the primary key of the Order table. The product field will reference the primary key of the Product table.
4. Make sure your fields are of the same data type as the primary key fields they will reference.

The example below is a finished junction table with some data. The order and product fields are both of the Number type, because they both refer to an Autonumber primary key field, which is in fact a Number field that is managed by the database.

The junction table has a primary key ID field and two foreign key fields. Each foreign key field references one side of the many-to-many relationship.

Implications

Now, let's look at the implications of this solution. Each record in the junction table ties a product to an order. Order 1 consists of just one product (4). Order 2 consists of 4 products, or does it? There are indeed 4 products in the order. That is, the quantity is 4. But, there are only 2 unique products in the order, because product 5 (the Nirvana Nevermind album) was added 3 times.

That is the implication of this solution: when a product is added to an order twice, this creates two records in the Order2Product table. This is fine. It is just good to be aware of this implication, because there are other solutions.

Often junction tables are created with a primary key that consists of the wor foreign keys (product and order). Let's look at a junction table that uses a composite primary key and a quantity field.

Create the junction table using a composite primary key

Let's create a junction table with a composite primary key in order to accomplish a many-to-many relationship.

1. Create a new table and call it LeftTable2RightTable, replacing LeftTable and RightTable with the names of your tables.
2. Go to the design view of your new table.
3. Remove the ID Autonumber primary key field that Access automaticcaly provides by selecting it and clicking the Delete rows button on the ribbon.
4. Add two new fields to reference the tables on each end of the many-to-many relationship.
5. Make sure each of your two fields have the same data type of the primary key fields they will reference.
6. Now, add a primary key including both these fields by following the steps below.
   
   1. Select both fields by placing the mouse pointer over the gray vertical bar on the left of the field definitions and dragging down, while holding the left mouse button down.
      
      
   2. While the fields still selected click the Primary key button on the ribbon.
      
      
      
      This will create a primary key that consists of the two selected fields.

Now you should have a junction table that is made of two fields that are both included in the primary key, like the one below.

Implications

There is an important implication of this solution. As you may know, primary keys must be unique accross all rows in a table. This means that it is now impossible to add the same product to an order twice. We can't have a (1,2) record twice for example, so we can only add product 2 to order 1 once with this solution.

The solution to this problem (or shall we call it a feature?) is to add a quantity field to the junction table, like this.

Now, when you want to add product 2 to order 1 twice, you just set the quantity field to 2.

This is how you would add multiple product to an order with the quantity field solution.

Create the actual relationships

The final steps in creating the many-to-many relationship is creating the actual relationships in the Relationships view. Creating a many-to-many relationship is a matter of creating two one-to-many relationships. Here is how you do it.

1. Open the Relationship view by going to the Database Tools tab on the ribbon and selecting the Relationships button.
   
   
2. In the Relationships screen, add the three tables you want to create the many-to-many relationship with to the screen. You can do this by dragging tables from the left onto the screen or by using the Show Table window, which is accessible via the Show Table button on the ribbon.
3. When your tables are on the Relationship screen drag the primary key fields of the entity tables (Order and Product in this example) to their counterparts in the junction table.
   
   
4. When you drag and drop a primary key field onto a field in another table, Access knows you want to create a relationship and it brings up the Edit Relationships window. This is where you create the actual relationship. In order to create a many-to-many relationship you must create two one-to-many relationships.
   
   
5. Create a one-to-many relationship between both your entity tables (Product and Order in this example) and the junction table. Select all three integrity options (Note: the integrity options are discussed in the article Create a one-to-many relationship in Access).

When both one-to-many relationships are created you have really created a many-to-many relationship, because a many-to-many relationship is made of two one-to-many relationships. The end result should look like this: