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.

The One to Many Relationship

In this post we are going to take a look at the One to Many Relationship in Access 2007.  We will go through the process of setting up a One to Many Relationship, but first, lets consider why they are necessary.

You may well be familiar with the term 'Relational Database' already, but perhaps don't understand what it means.  If that is the case, then, hopefully, this should put you on the right track to penetrating the meaning of the term.  Relational Database design is all about how database tables are connected with each other. Whilst the novice database designer will create one single table with lots of fields, the relational database designer will look at the the data that needs to be stored and will put it together in a number of groups.  Each group is then made into a separate table.  But because the subjects of the data stored in separate tables are connected in the real world - for example customers and orders - we need to create a relationship between the tables within the database itself.

Lets take a look at how we go about doing this in practice.  We will use a simplified example of a customer/order database.

Imagine we have a number of customers who order electronic products by email, and we want to create a database to record both the customer's details and their orders.  As we mentioned in the example above, we would go about this by separating the customer details and orders into two separate  groups which we then make into tables.  We do this for the simple reason that if a given customer makes more than one order, we only have to enter the customer details once in the customer table.  Because we are going to create a relationship between the customer table and the orders table, all orders for individual customers will be connected with the correct persons' customer details. This is made possible by connecting the primary key field of the customer table with the corresponding foreign key field in the orders table.  If you remember, Primary and Foreign Keys were covered in my last blog post.

1. The first step is to create a new Access Database. Lets call it Customer Orders.
   
   
2. Next create a new table called tblCustomers with the following fields - ID (autonumber, primary field), FIRSTNAME (text), SURNAME (text), ADDRESS1 (text), CITY (text), and POSTCODE (text).
   
   
3. Create a new table called tblOrders with the following fields - ORDERID (autonumber, primary field), ITEMORDERED (text), DATE (date), PRICE (number, set the format property to currency), CUSTOMERID (number).  CustomerId is going to be the foreign field when we create the relationship.
   
   
4. To create the relationship, select the DATABASE TOOLS RIBON  and click the RELATIONSHIPS icon (from the SHOW/HIDE group).
   
   
   
   
5. This opens the RELATIONSHIPS window.  You will also see the pop up SHOW TABLE form the first time you open the window.   We are now going to select which tables are going to be used in the relationship.
   
   
   
   
6. There are just two tables in our example database.  Click on tblCustomer to highlight it in blue if it is not highlighted already.  Then click add.  Do the same for tblOrder, then close the pop up SHOW TABLE form.You should now see the two tables represented as separate box's in the RELATIONSHIPS window.  You will notice in the diagram below that each box has it's fields listed and primary keys indicated.
   
   
   
   
7. We will now click the ID field in tblCustomer and drag it to the CUSTOMERID field in tblOrder.  Notice the mouse pointer image changes from a circle with a line across to a plus sign as it hovers over the fields in tblOrder.  When you release the mouse button at the end of the click and drag operation,  a new EDIT RELATIONSHIPS pop up form opens.
   
   
   
   
8. Click the three tick boxes which say ENFORCE REFERENTIAL INTEGRITY, CASCADE UPDATE RELATED FIELDS, and CASCADE DELETE RELATED RECORDS. We won't be going into what these do in this blog post.
   
   
9. Click the CREATE BUTTON.  Our table relationship is now in place.  This is what the database window looks like now.  Note the line drawn by Access between the ID field of tblCustomer and the CUSTOMERID field of tblOrder.
   

All we need to do now is enter some data and test out the relationship.  Try entering this data from the image below into tblCustomer:

Then enter this data from the image below into tblOrder:

Notice that each order in tblOrder has a CustomerId corresponding to one of the customer ID's in tblCustomer.  This is how we link each order to a particular customer.  To prove that this works, open up the tblCustomer table and click the + sign's (left of table) on each customers row.  This shows that Access has automatically linked each order from tblOrder to it's corresponding customer in tblCustomer.

All this gives us a basic idea of what table relationships are all about. There is, however, much more to it than this.  For example, once we have set up relationships like this, we may create forms, queries and reports that reflect these relationships, and exploit the advantages that this has over single table "flat file" database applications.  In the next blog post, I hope to write about creating a single Access Form which allows us to enter data into both Customer and Orders tables together.