Friday, 29 April 2011

The Many to Many Relationship

Last month we learnt how to create a comparatively simple One to Many Relationship.  In this post we shall go one step further and create a Many to Many Relationship in the context of a Customer Orders database.

The Many to Many Relationship differs from the One to Many, in that the latter involves just two tables.  The table on the One side of the relationship potentially having multiple corresponding records in the table on the Many side.  It is depicted in the Access Relationships window like this:

 Figure 1: The One to Many Relationship.
By contrast the Many to Many Relationship consists of three tables.  The two tables on the Many sides of the Relationship are joined to a third Junction Table connecting them together.  It is depicted in the Access Relationships window like this:

 Figure 2: The Many to Many Relationship.
In the screen-shot above, we see that tblOrder and tblProducts are joined together in a Many to Many Relationship.  TblOrderDetails is the third Junction Table mediating between the two. This mediation takes the form of two ordinary One to Many Relationships, with the junction table set up to be on the Many side of both. Let us now examine the reason why this is so.

When a customer makes an order, there may be a number of products purchased.  As such we have a One to Many scenario - One order potentially 'containing' Many products. However, one type of product may also appear in a number of different orders.  This too, is a one to many scenario, but this time going in the opposite direction. As such, we need the Junction Table in between to record each product appearing on a particular order, and each order where a particular product appears.

One more thing: you may notice that there seems to be two primary keys pictured in the Junction table of Figure 2 above.  These are also the foreign keys from the other two tables in the relationship. There is really only one primary key, but it consists of the unique combination of the two primary keys from the two tables on the Many sides of the relationship.  As such, one particular product type can only appear in any particular Customer Order once, and vice versa.

To understand this more clearly, you may find it helpful to visualise the layout of an invoice.  Each individual invoice pertains to a particular order for a particular customer.  The invoice includes a list of all the different products purchased for that order.  The same product types may also appear on different invoices (for the same or different customers).  The data from these lists are derived from the Junction table.

Creating a Many to Many Relationship

Lets have a go at creating a Many to Many Relationship ourselves.  We will begin by downloading a database containing tblOrder and tblProducts .  There is also another table called tblCustomer which is joined to tblOrder in a One to Many Relationship.  We will then create the tblOrderDetails Junction Table and the Joins which make up the Many to Many Relationship.

Setting up the Exercise
1. Download or create the tables to be used in this exercise - ManyToMany_Exercise Tables.
2. Open the database and select the DATABASE TOOLS ribbon.
3. Click the RELATIONSHIPS icon in the SHOW/HIDE group.  Here are the tables we are starting off with.

 Figure 3: Start of Many to Many Exercise

Create Junction Table
1. Select the CREATE tab on the Access Ribbon.
2. Click the TABLE DESIGN icon from the TABLES group.
3. Enter the first two fields in the TABLE DESIGN GRID.  This is OrderId and ProductId.  Select NUMBER as the DATA TYPE for both fields.
4. We now need to designate both these fields as the joint primary key.  Select both rows by holding down the CONTROL key on your keyboard and clicking the two blue sections on the far left of the Grid.  Both rows are then highlighted.  You can now click the PRIMARY KEY icon in the TOOLS group of the DESIGN ribbon.  Two primary key symbols appear now on the far left of the two rows.

 Figure 4: Selecting the Primary Key on two fields.

5. Add the remaining two fields to the Grid.  These are Quantity (DATA TYPE: NUMBER) and CostPerUnitPaid (DATA TYPE: CURRENCY).
6. Close the table, saving it as tblOrderDetails when prompted.
Creating the Joins
1. Select the DATABASE TOOLS tab on the Access Ribbon.
2. Click the RELATIONSHIPS ICON in the SHOW/HIDE group.
3. The Junction Table we just created cannot be seen at first.  To make it visible, click the SHOW TABLE icon in the RELATIONSHIPS group of the Access Ribbon.  Double click tblOrderDetails from the SHOW TABLE dialogue box to add it to the RELATIONSHIPS window.  Close the SHOW TABLE dialogue box.
4. Click and drag the new table to a location between tblOrder and tblProducts.
5. We are now going to create a One to Many Relationship between the ID field of tblOrder, and the OrderId field of tblOrderDetails.  Do this by clicking  the tblOrder.ID field, and dragging over to tblOrders.orderId.  When you release the mouse key the EDIT RELATIONSHIPS dialogue box opens.  Click the three tick boxes and click the CREATE button to create the relationship.

 Figure 5: The Edit Relationship Dialogue Box.

6. Next we need to create a One to Many Relationship between the tblProducts.ID field and tblOrderDetails.productId.  Follow the same procedure as above to create this relationship.
The Many to Many Relationship is now in place.  Your RELATIONSHIPS window should now look like this:

 Figure 6: The Many to Many Relationship.

Lets end this post by taking a brief look at the Many to Many relationship in action.  Obviously we have not created any tables or reports to enter or display information in a user friendly manner (this is something I hope to cover in a future post).  We can, however, see how all this information is connected together using the example of a single order.

The screen-shot below shows an order which I created for one of the customers in the customer table.

 Figure 7: Three nested tables showing an order made by customer Tracey Smith.
As you can see there are three nested tables. The top level table shows the customer record from tblCustomer.  The name of the customer here is Tracey Smith and her customerId is 2.  The mid-level table shows the order record from tblOrder. There is just one order on the database for this customer.  The order ID is 18, and the orderDate is 28/4/2011.  The inner table shows the order details from tblOrderDetails. As you can see there are four product items for this order.  The product ID's are 3,2,1, & 5. The customer ordered one of each item (see the quantity field).

In the next screen-shot (figure 8) we see the products table. Each row is expanded to show a set of records from tblOrderDetails corresponding to them.  As such, we see the orders within which each product appears:

 Figure 8: The Products Table (tblProducts) showing the order details (tblOrderDetails) for each item.

As you can see, Order 18 (which we looked at above from the Customer Order side of the Many to Many Relationship) is seen here from the Product side, along with all other orders.  If you look carefully you will see that an instance of OrderId 18 appears under each productId (ie 3,2,1, & 5) which we mentioned above.  As such we see how the data contained in our tables appears from both sides of the Many to Many Relationship.

Of course, this is a somewhat fragmentary perspective from which to view our data.  That is why we need  forms, queries, and reports to enter, process and display all this information in a user friendly manner.  This is something that I hope to cover in a future post.

1. This was a helpful tutorial; I was having trouble understanding a many-to-many relationship but, thankfully, it is clearer now. Or, at least, starting to become clearer...

d(^_~)

2. Thanks Kane. Glad you found this tutorial useful. The Many to Many Relationship can be hard to grasp at first, but it does become much easier the more you work with it.
Best of luck,
Justin

3. I cannot put two keys in the table (Access 2007)
Luis Danyau
lhdanyau@gmail.com

4. Hi Luis

What you need to do is click the blue square (where the primary key symbol appears) of the first field, then hold the *control key* on the keyboard whilst clicking the blue square of the second field.

Then, whilst the two fields are both highlighted, click the Primary Key Icon (located in the TOOLS group of the DESIGN ribbon). This creates the composite primary key ie both fields are the primary key together.

5. Hi Justin
Yes, I follow up what you told, no problem.
Thankyou
Luis

6. Your tutorial is great. My problem now is setting up a form to input different orders with multiple products...do you have another tutorial taking this model to the form/report level?

7. Hi dcodding.

Thanks for your question. The Many to Many Relationship is basically two One to Many Relationships joined together by a junction table. The kind of set up your describe has an Orders table, a Products table and a third junction table to record specific instances where a given product appears on a given order. This is the Order Details table. If I am understanding you correctly, we need to create an order form with a subform for the Order Details. Just think of this as a One to Many Relationship between these two tables (ie one order has many order details records). There is a tutorial showing you how to do this here: - http://ms-access-tips.blogspot.com/2011/04/subforms-viewing-one-to-many.html

Then we need to create a combo box on the Order Details subform so the user can select a product from the Products table (on the other side of the Many to Many Relationship). It is advisable to customise the combo box so that the ProductId primary key field is the bound field on the combo box. However, because this may be a number (not very user friendly) we can get the the combo box to display the actual product name whilst still being bound to the ProductId. I have also done a tutorial on customizing combo boxes which should be helpful: - http://ms-access-tips.blogspot.com/2011/07/customizing-access-combo-box.html

Hope this helps

1. Just want to add that I have now published a full post on Creating a Form for a Many to Many Relationship. Here is the link:

Creating a Form for a Many to Many Relationship

8. its so hard to do another database and make a many to many relationship,can a students general information and subjects data can have a many to many relationship?can i have an example?
thank you.

9. Hi Alyssa.

Yes, you can create a Many to Many Relationship for students and subjects. I would create a table for the STUDENTS and then a table for SUBJECTS. I would then create a JUNCTION table with at least two fields - these will be the foreign key's from the STUDENT and SUBJECT tables (they may also be made the composite primary key for the JUNCTION table).I would then create a One to Many Relationship between the STUDENTS Id field, and its foreign key id field in the JUNCTION table. I would then do the same between the SUBJECTS Id field, and its foreign key id field in the JUNCTION table.

10. Justin,

Thanks for the clear description of how to handle a many-to-many relationship.

My situation is that I have a table of Instructors who can have multiple areas of expertise (i.e. Programming and Networking) and a table of courses (with one area of required expertise defined).

A. How would you design the database to allow a query to determine which classes an instructor can teach?
B. If it requires a Junction table, is there a way to semi-automate the population of that table? For example, an update or append query that will scan the Instructor & Courses tables and populate the Junction table with the courses they can teach.

Thanks!! Bernie

1. Hi Bernie

You could structure your database with a Many to Many Relationship between the EXPERTISE table and the INSTRUCTOR table (as each instructor can have multiple skills and each skill can be present in multiple instructors). Since each course has only one defined skill associate with it, you can have a One to Many Relationship between the EXPERTISE table and the COURSE table (with the EXPERTISE table being on the ONE side of the relationship).

Once you have created the data entry forms, you would need to manually select each defined skill from a combo box for each of the courses (for the COURSES table), and then manually select which skills each of the instuctors has from a combo box (for the INSTRUCTOR SKILLS junction table).

Once all the data has been entered/selected, you can then create a query (based on all four tables) to list which courses can be taught by which instructors.

11. Dear Justin,

Thank you for your tutorial. It's been very helpful in getting me to finally understand many-to-many relationships.

Using your example, I am able to query all orders that include a specific product. Tell me, would there be a way to query all orders that include two specific products, say, those with productId 1 and 2? How would I go about doing this?

Thank you!

Steve

1. Hi Steve

Yes, it is fairly straightforward to query all orders which include the two specific products that you mention. To do this we would select at least two tables from the many to many relationship - that is, the Order Table and the Order Details Junction Table. We would then apply the criteria against the ProductId field of the Order Details Junction Table. Simply enter the following criteria in the Criteria Row of ProductId on the Query Design Grid:

= 1 Or 2

This will return all orders containing products with Id's 1 Or 2.

Due to other professional commitment I have decided to stop taking questions for the foreseeable future. Apologies to anybody who did not get their questions answered.

Justin