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:
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.
- Select the CREATE ribbon.
- Click the QUERY DESIGN icon located in the OTHERS group.
- 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. - Select the Surname and FirstName fields from tblCustomer; and ItemOrdered and Date from tblOrders.
- Enter <#05/03/2011# as the query criteria in the Date column of the query grid.
- 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.
Thank you Piotr D. for pointing out that I had used the ID column instead of the Surname column in one of the screen shots. This has now been rectified.
ReplyDeleteThanks again.
Justin