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.

The Outer Join Query

In my last blog post we learnt about the Inner Join Query, and had a go at creating one.  In this post we are going to do the same, but this time with the Outer Join Query.  Let us begin by looking at how the two different Join types differ.

Query Joins in general connect two or more tables so that the query results present data as though it is from a single table.  This is done in a coherent manner whereby corresponding records from separate tables are presented with each row of data in the query results matching.  In the previous post on Inner Joins we used the example of a Customer Table and an Order Table to produce a single row of data which displayed a Customer Name with his or her Order.  For a record to appear in the query results the Inner Joine required each customer (in tblCustomer) to have at least one order (in tblOrder) and vice versa.  It is at this point that Inner Join and Outer Join Queries differ.

Suppose for example, we had a number of customer records with no corresponding orders in the orders table (lets say the orders were accidentally deleted at some point).  If we run an Inner Join Query, those customers without order records would not show in the results.  However, if we run an Left Outer Join Query all customers would appear regardless of whether or not they had corresponding records in the order table (provided, of course, the customer met any query criteria which was set). If a customer had no corresponding order records his or her query results row will just contain the customer's details with empty fields in place of order details.

This brings me to one last point: we need to specify whether the Join is a Left Join or Right Join.  So what does this mean?  Basically by selecting whether the Outer Join Query is left or right, we are letting Access know which table in the Join is going to display all the rows matching a given criteria, and which side will only display data corresponding to that chosen table.

Lets now have a go at creating an Outer Join Query.  The exercise below uses two tables, tblCustomer and tblOrder.  We are going to use the FIRSTNAME, SURNAME and CITY fields from the customer table, and ITEMORDERED and DATE from the orders table.  Our query criteria will filter out all customers who live in Bolton.  You can download an Access Database with these tables here, or enter them manually from the screenshots below:

Above: Date for tblCustomer.
Below: Data for tblOrder.

NB There is a One to Manly Relationship between the two tables
between tblCustomer.ID and tblOrder.CustomerId.

How to Create an Outer Join Query


We are going to do this exercise in two stages.  The first stage is to create a standard Inner Join query using the Access Query Design Grid. In the second stage with will modify the query so it becomes an Outer Join Query.


Stage One - Create an Inner Join Query.

1. Open the database you downloaded above.
2. Select the CREATE tab on the Access Ribbon.
3. Click the QUERY DESIGN icon in the OTHER group.
4. Select tblCustomer and tblOrder from the SHOW TABLE dialogue box.  Then close the dialogue box.  Both tables should now have appeared in the top section of the QUERY DESIGN GRID.  Since there is already a one to many relationship between the two tables, this is represented in the Query Design Grid as an Inner Join.
5. Select fields FirstName, Surname, and City from tblCustomer.  Then select fields ItemOrdered and Date from tblOrder.
6. Enter the query criteria ="bolton" in the CRITERIA row of the CITY column in the lower section of the grid.  

Your query should now look like the screen shot below:

If you run the query now (before we change it to an Outer Join Query) you should get this result shown below:

Above: the query run as an Inner Join before modification.

You may notice that although our customer table contained two records with Bolton as the City (our criteria), only one record was displayed.  This is due to the fact that we ran the query as an Inner Join and the excluded record that has no corresponding order in the tblOrder table.

Lets see what happens when we change the query to an Outer Join type:

Stage Two - Modify the Query to an Outer Join.

1. Open the Inner Join query we just created in DESIGN VIEW.  The QUERY DESIGN GRID opens.
2. Double click the Join between the two tables to bring up the JOIN PROPERTIES dialogue box.
   
   

   

   The JOIN PROPERTIES dialogue box.

   
   Notice how the Join Properties box shows the Left Table Name (tblCustomer) and the Right Table Name (tblOrder). This ties in with what we discussed above regarding Left and Right Joins. We want all customers to be displayed regardless of whether they have a corresponding record in the orders table.  As tblCustomer is the Left Table shown in the JOIN PROPERTIES BOX we need to modify our query to a Left Outer Join.  To do this we need to click Option Two where it says "Include ALL records from 'tblCustomer' and only those records from 'tblOrder' where the joined fields are equal".  
3. Click Option 2 in the JOIN PROPERTIES dialogue box and click OK.

Your Left Outer Join is now in place.  It is represented in the Query Design Grid like this:

Representation of the Left Outer Join in
the Query Design Window.

Notice the joining line between the two tables now has an arrow pointing from left to right, ie from tblCustomer to tblOrder.  Our query will show all records in tblCustomer (matching our ='bolton' criteria, and only orders corresponding to those customers matching the set criteria.  Lets run the query and see:

Results from the modified Left Outer Join Query.

Now both customers from Bolton appear in the query results.  As you can see, Andrew Johnson's record has no corresponding records in the orders table.  When we ran the Inner Join Query previously, this customer did not feature in the results for that reason.  Now that we have modified it to a Left Outer Join Query, this customer record is no longer excluded.

Now then, suppose we removed the criteria from this query and added a new order record in tblOrder without a corresponding customer record.  What do you think would show if we ran the Left Outer Join Query again?  Please feel free to try this.  You will see that all customers with their corresponding orders are displayed.  However, our new order record (without a corresponding customer) is excluded from the query results.  See what happens next if we modify the Join to a Right Outer Join Query.  Do this by :

1. Opening the Query Design Window.
2. Double clicking the existing Join line between the tables to open the JOIN PROPERTIES dialogue box.
3. Select Option Three where it says "Include all records from 'tblOrder' and only those orders from 'tblCustomer' where the join fields are equal"

This has changed our Query to a Right Outer Join.  Trying running the query now.  

Query results when from a Right Outer Join.

The new order record now appears in the results whilst the customer record with no order is now excluded.  This is the difference between a Left Outer Join Query and a Right Outer Join Query.  As such we see that having a Left or Right Join determines which table is effectively 'leading' the query.

Basing a Query on Two Related Tables - The Inner Join

If you have been following my last two blog posts you will know that we have been looking at relationships between Access Database Tables.  In this post we are going to examine how we can base a Query on two related tables using what is know as The Inner Join.

Before we go into the practicalities of setting up an Inner Join Query, let us first consider what the Inner Join actually does with the tables involved.  Basically, the Inner Join connects two (or more) tables so that the results of the Query present data as though the tables involved were one.  As such, we get a single list of row headings with fields from both tables, with the corresponding data displayed below.  The data from the two separate tables matches in each row of data, so, for example, a customer name from a Customer Records table will match his or her order in the Orders Table like this:

Results from an Inner Join Query.

Surname and FirstName fields come from the Customer Table.

ItemOrdered and Date fields come from the Orders Table.

The data from the two fields match correctly due to the Inner Join.

One more thing about Inner Join's: for a record to be included in the query results, there must be corresponding information in both tables involved in the Inner Join Query. So, for example, if we had a customer record without any corresponding orders in the orders table, that customer will be omitted from the query results (even though he or she may have met the Query criteria).  This distinguishes the Inner Join from the Outer Join query type.  In an Outer Join Query, a customer record would be included in the results provided he or she met the query criteria.

We will now have a go at creating an Inner Join Query using the Customer and Orders tables referred to above.  These tables will be familiar to you if you have worked through the exercise on my post about The One to Many Relationship.  You can download the database tables to be used from here or create them yourself by working through above mentioned exercise.  The tables contain the following data:

Above: tblCustomer.
Below: tblOrder.

There is a One to Many Relationship between ID in tblCustomer (the one side of the relationship), and CustomerId in tblOrder, (the many side of the relationship).  Once you have the tables in place, you can begin the exercise on creating an Inner Join Query.


How to Create an Inner Join Query


In this exercise we are going to select FIRSTNAME  and SURNAME from frmCustomer; and ITEMORDERED and DATE from tblOrders.  The purpose of the query is to create a list of customers with orders made before 5th March 2011.

1. Select the CREATE ribbon.
2. Click the QUERY DESIGN icon located in the OTHERS group.
3. When the SHOW TABLES dialogue box opens, double click tblCustomer and tblOrders to select these two tables.  Then close the dialogue box.  You should see both tables appear in the top section of the Query Design Window:
   
   
   
   
   
   Notice how the existing relationship between the tables appears automatically.  This is the query's Inner Join.  If we wanted to, we could delete or modify the Join without affecting the actual relationship between the tables. The Inner Join (or any other type of Join) only represents how the tables are connected within the Query.  However, we can leave the Join unmodified - only Outer Joins or a bespoke Inner Join between other queries or tables would require modification at this stage.
4. Select the Surname and FirstName fields from tblCustomer; and ItemOrdered and Date from tblOrders.
5. Enter <#05/03/2011# as the query criteria in the Date column of the query grid.
   
   
   
   
   
6. You can now run the query.

And here is the result:

The SURNAME and FIRSTNAME fields come from tblCustomer. ItemOrdered and Date fields come from tblOrder. The date criteria filters out orders prior to 5th March 2011 with the corresponding customers. The data from the two fields match correctly due to the Inner Join.

Subforms - Viewing the One to Many Relationship in Action

In my last blog post we learnt how to create a One to Many Relationship between a Customer Table and an Orders Table.  We examined how Access was able to separate groups of orders according to which customer they belonged to.  This was possible due to the creation of a Primary Key Field in tblCustomer and a corresponding Foreign Key Field in tblOrder; and then creating a One to Many Relationship between the two tables. In this post we are going to examine how using a Subform enables us to view this One to Many Relationship in action.

To do this we will begin by creating the main form for the Customer Table (frmCustomer).  This will display each customer record, and corresponds to the One side of the One to Many Relationship.  We will then create a new form for the Orders Table (frmOrders) which will display in Datasheet View.  This is going to be used as the subform contained within frmCustomer.  It will display a list of orders for each individual customer. This corresponds to the Many side of the One to Many Relationship. The finished result will look like this:

The top section of the form shows the Customers Details.
The Subform below show the orders made by that Customer.

If you havn't already done so, you can create the tables and the relationship by following the instructions in the previous post on The One to Many Relationship.

Creating the Orders Form

The first step in this exercise is to create the orders form - that which will be used as the subform. We will call this frmOrders.

1. Select the CREATE TAB on the Access Ribbon.
2. Click the FORM DESIGN icon in the FORMS GROUP of that Ribbon.
   
   
   This opens up a blank Form Design Grid. (For more information about the form design grid, see my post on Creating an Access 2007 form from Scratch).
   
   
3. Select the whole form by clicking the square at the top left hand corner of the FORM DESIGN GRID  (where the horizontal and vertical rulers meet). When you do so, a smaller back square appears in the middle.
   
   
   
   
   
4. Click the PROPERTIES SHEET icon in the TOOLS group of the DESIGN ribbon.  
5. Select the DATA tab on the PROPERTIES SHEET.
6. Set the RECORD SOURCE property to tblOrder.
   
   
   
   
   
7. Click the ADD EXISTING FIELDS icon in the TOOLS group of the DESIGN ribbon.
8. Click the EXPAND button (the + sign in a small box) by tblOrders so the list of field opens out for that table (if it has not already done so).
   
   
   
   
   
9. Select the ItemOrdered, Date, and Price from the FIELD LIST by double clicking each one in turn. As we do so, they appear in the FORM DESIGN GRID like this:
   
   
   
   
   
10. We are now going to change the DEFAULT VIEW property of the form.  So select the FORM again by following step three above (if it is not already selected).
11. Open the PROPERTIES SHEET (if it is not already open).
12. The DEFAULT VIEW property is located on the second line down of the FORMAT tab of the PROPERTY SHEET.  Change the property to DATASHEET by clicking the DEFAULT VIEW field;  then clicking the arrow at the end of the box; and selecting that option from from the drop down list.
    
    
    
    
    We use the DATASHEET option here so that our subform displays as a list within the main form, thereby reflecting the One to Many Relationship that we want to show in action.

You can now close the form, saving it as frmOrders (if you have not already done so).


Creating the main Customer Form


Next we are going to create the main customer form to display the customer details.  This form will also hold the Orders Subform, thereby listing all the orders for each particular customer.  Steps one to nine below correspond to the steps we went through creating the orders form.  Step 10 onwards is new, and deals with the creation of the Subform Control.

1. Select the CREATE TAB on the Access Ribbon.
2. Click the FORM DESIGN icon in the FORMS GROUP of that Ribbon.
3. Select the whole form by clicking the square at the top left hand corner of the FORM DESIGN GRID  (where the horizontal and vertical rulers meet). When you do so, a smaller back square appears in the middle.
4. Click the PROPERTIES SHEET icon in the TOOLS group of the DESIGN ribbon. 
5. Select the DATA tab on the PROPERTIES SHEET.
6. Set the RECORD SOURCE property to tblCustomer.
7. Click the ADD EXISTING FIELDS icon in the TOOLS group of the DESIGN ribbon.
8. Click the EXPAND button (the + sign in a small box) by tblCustomer so the list of field opens out for that table.
9. Select the FirstName, Surname, Address1, City and PostCode from the FIELD LIST by double clicking each one in turn.
10. We are now going to create the Subform.  We are going to do this process manually so begin by deselecting the USE CONTROL WIZARDS icon from the CONTROLS group of the DESIGN ribbon. It is deselected when it is no longer highlighted in orange.
    
    
    
    
    
11. Click the SUBFORM icon in the CONTROLS group of the DESIGN ribbon.
    
    

    

    The SUBFORM icon is on the bottom row,
    highlighted in orange.

    The mouse pointer changes to the Add Subform Icon.
12. Take the Add Subform Icon to a location below the other text fields in the FORM DESIGN GRID, and click.  This creates an empty subform control on your main form.  At this stage it just looks like this:
    
    

    

    The empty Subform Contol is represented in this
     image by the unbound control labelled Child1.

    
    
13. It is a good idea at this point to resize the control to accomodate the subform.
14. Next we are going to set the Subform Control's properties to display tblOrders (which we created above) as the actual subform.  NB you might like to note the distinction here between the Subform Control and the Form which is displayed in that control. The later is a property of the former.
15. Click the Subform Control on the Form Design Grid to select it. The border changes to an orange highlight once it is selected.
16. Click the PROPERTIES SHEET icon in the TOOLS group of the DESIGN ribbon.  This will display the PROPERTIES SHEET for our Subform Control.
17. Select the DATA tab of the PROPERTIES SHEET.
18. Now set the SOURCE OBJECT property to tblOrders.  Notice how Access has filled in the LINK MASTER FIELDS property below to ID, and LINK CHILD FIELDS property to CustomerId.  The Master Field is the Primary Key Field (ID) of tblCustomer, and the Child Field is the Foreign Key field (CustomerId) in tblOrder.  These can be entered manually if necessary.  

This has now configured our Subform Control to display a list of orders for each individual customer.  At this stage you might like to tidy up the form, and enter any labels or dividing lines that you see fit. When you have done so, save the form as frmCustomer, and open it in FORM VIEW to see what we now have.  It should look something like this:

As you can see in the screenshot above, the customer, John Jones has three orders displayed in the Orders Subform.  As you move through each customer record in turn, you will see a different set of orders corresponding to the particular customer being viewed. You will see that each customer in frmCustomer contains the orders we entered when we worked through the exercise in the last blog post on the One to Many Relationship.  So to see the One to Many Relationship in action, just compare your results from frmCustomer with this screenshot of the data held in our two tables:

As such, by using a Subform we are effectively processing information from both related tables at once.  This makes your database application much more user friendly.  Try entering some new orders for the customers in this database, then look at the orders table to see them stored. Notice how Access enters the customerId in the orders table automatically so it know which customer it belongs to.  All this is part of the One to Many Relationship in action.