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.

Automating an Update Query Using DoCmd.RunSQL

This tip shows us how to create and execute an Update Query using the VBA DoCmd.RunSQL Statement.  We shall use the example of a Products table, and our objective is to update the CostPerUnit field of a particular record matching a set criteria.  To do this the user will open an unbound form to select a product from a combo box, and enter a new updated price in the textbox.  

Above: Unbound Form to select a product from a Combo Box list, and enter its new price.

The code which creates and executes the SQL Statement will run when the Update command button is clicked.  Information entered by the user is referenced by the code (as Me!ctlProduct and Me!txtNewCost) and integrated into the SQL statement.

Above:  This code runs when the users click the Update command button.

The varSQL variable contains the SQL from the Update Query which we are going to run.  This can be built using the Access Query Design Grid, and switching to SQL view to copy the statement.  The two form controls with our user input are built into this variable, and become part of the Statement. The next line uses the DoCmd.RunSQL statement to the run the SQL contained in our string variable.  This will then act like a standard Update Query: the relevant product is found, and the CostPerUnit field is updated to the new price specified by the user.