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.