Walk-Through - Creating Many-to-Many Relationships in MS Access

This article follows on from our Stock Management Database tutorial, so if you have not yet read this article, click here to start there first. If you would like to create your own many-to-many relationships then read on....

In this article we will be discussing many-to-many relationships and how they can be used to allow your database to cope with more complex situations. We have included examples of where you need to use many-to-many relationships based on our many years of experience creating Access databases for real businesses. To download the database we'll be making in this tutorial for free, click on the link above. You can also follow this tutorial using our YouTube video that explains each step and provides demonstrations to help you do it yourself. Click on the link above to open the video in a new tab/window.

Before you get started
Please note that in order to follow this walk-through, or run the free finished database linked above, you must have a full version of Microsoft Access installed on your computer.

Introduction
In the stock management database we created in our previous article, we used the example of a database managing supplies of Parts. As you may remember from that article, we kept things simple by assuming each Customer only ordered one Part at a time. The problem is that this is too simplistic for most businesses, as Customers will often order many Parts from a Supplier on one Order.

How to solve the problem
In our previous article, we linked the Parts and Orders tables directly through a one-to-many relationship. This relationship will allow each Part in the Parts table to be on many orders, but each Order can contain only one Part. If Customers want to order several Parts at once from you, this will not work. What is needed here is what is called a many-to-many relationship.

You cannot create a direct many-to-many link in Access between the two tables, so to create this relationship, you need an intermediate table between the two. In this case we are going to call it Order Items. The Orders table contains a record for each time a Customer places an Order with you. The Order Items table lists the Parts requested on each Order. Each of your tables, Orders and Parts, link to this table with a one-to-many relationship. So, each Order can link to multiple Order Items, and each Part can appear in multiple Order Items. This will allow you to receive orders of several different Parts at a time from Customers.

The new table needs to have its own unique primary key, like all tables, and then a field that is the same as EACH primary key of the linking tables. Let's see this in practice.

Creating the table
As before, create a new table and create a unique ID field. As this is a joining table, there is no specific number or code to use as a primary key, so just create a separate Autonumber ID field for this, called "OrderItemID".

Next, create the fields that will link to the Orders and Parts tables. These should be the same names and field types as the primary key fields in those tables, which are OrderNo and PartNo respectively.

One thing to remember is that there may be fields in the Orders table that should move into the new OrderItems table because they are specific to each line on the Order, rather than to the Order itself. For us the OrderQty field in the Orders table is an example of this; as the Customer will specify the quantity of each Part that they want. You will need to move this from the Orders table, and add it to the Order Items table. Note that, as each Order will be from a single Customer, and on a single date, the other fields, CustomerRef and OrderDate should stay in the Orders table. We don't have any other fields to move in our sample download, but you may in your database.

Relationships
Once you have created your table, you can link up the tables. As in our previous article, use the lookup wizard to create these relationships. For the OrderNo relationship we might want to see more than one field in the lookup to determine which Order the items are linked to, so we will add the Customer and the Date to this.

Doing the same for the PartNo, we have now created our relationships.

Going to the Relationships Window we can now enforce referential integrity as before and add cascade updates. You may remember from the previous article we discussed cascade deletes. In this case the relationship between Orders and Order Items is one situation where we would usually enable this, since Order Items without an Order are meaningless, and if you want to delete an Order then you will also want to delete all Items on the Order. Note that this is not the case for the relationship between Parts and Order Items. Without this setting, you will not be able to delete a Part that appears on an Order Item record. This is safer so you don't lose Order Item details

And that's all there is to it! Now we can enter an Order and enter several Items for each Order at a time. Note that in our download associated with this article we have also added in another many-to-many relationship, between Parts and Purchase Orders. This will allow you to have several different Parts on a particular Purchase Order.

There are other locations within the database where a many-to-many relationship might be useful, for example between Suppliers and Parts. If you order a particular Part from several different suppliers (due to cost, or quantity), and each Supplier supplies several different Parts, this will require a Supplier Parts table between the two. Using the skills you have gained from this article, try adding this table yourself if this is something that might be useful to you.

We hope you enjoyed this article. If you decide that creating your own database isn't for you, and would like some advice or assistance, call us on 01747 822616 for a free consultation. You can subscribe to our YouTube Channel for more tips on using Microsoft Access. Thanks for reading!

If you enjoyed this article or found it useful, why not tell others about it?