The Access Query Designer does some great stuff but what it won’t do is an Outer Join.
What is an Outer Join? If we have two tables, in this example Sales and Orders and we want to see a full list of customers who have placed Either an Order or a Sale and also see the details of both. And not only that, we want to see what they haven’t done.
The outcome is that we can create a list that shows us people who have made a purchase but not placed an order and vice versa. Access can easily do the first bit but can’t do the vice versa.
You can download the example database here – Outer Join
To start let’s look at an Inner Join.
This will list only those customers who have both placed and order and made a purchase. And I’m sure you already know how to do this.
Drag the two tables from the nav pane to the query design window.
Select and Drag Customer_Name from one table to the other to create a join.
If you select the join and right click on Properties you will see that the first option is selected.
Here is the resulting data
And the SQL looks like this
SELECT tblSales.Customer_Name, tblSales.Sale_Date, tblOrders.Order_Date
FROM tblSales
INNER JOIN tblOrders
ON tblSales.Customer_Name = tblOrders.Customer_Name;
Left Join
You’ve probably already used Left (or Right) joins before. In this example we are going to return all the records from Sales and only those from Orders where we have a Sales record for the customer.
The Result looks like this –
And the SQL look like –
SELECT tblSales.Customer_Name, tblSales.Sale_Date, tblOrders.Order_Date
FROM tblSales
LEFT JOIN tblOrders
ON tblSales.Customer_Name = tblOrders.Customer_Name;
You will note that there are Null values for Order Date where the Customer has no orders.
Outer Join
So how do we get the Customers without Sales as well as Customers without Orders?
Lets start by creating a UNION Query, The Query Designer can’t create or show the design of a Union query so you will need to do this in SQL view.
SELECT Customer_Name FROM tblSales
UNION
SELECT Customer_Name FROM tblOrders;
Save this as qryCustomers
Now when you save this and view it you will see that all names from both tables are listed whether they are in the other table or not. The other interesting thing about a UNION query is that it returns only one instance of each name. Also note that you must have the same number of columns in each SELECT.
Now go back to the query designer and drop this Union query in between the other two tables.
Create a Join from the UNION query to the Customer_Name field in the other two tables. At this point we need to select each join and right click on properties then select the option to return all records from the Union query and only those from the table where the join is equal.
Then change the Customer_Name column to come from qryCustomer. You should now have –
Now you will get all records from both tables whether they exist in the other or not.
And as you can see, Cheryl has orders but no sales and Liz has Sales but no Orders.
In this example I’ve used the Customer Name, so it’s easier to see what is going on, but if you have a Customer ID column (and you should) then even better to use that.
And that, dear reader, is an Outer Join.