Outer Joins in Access

By | December 11, 2015

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.

OuterJoin01

If you select the join and right click on Properties you will see that the first option is selected.

OuterJoin02

Here is the resulting data

OuterJoin03

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.

OuterJoin04

The Result looks like this –

OuterJoin05

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.

OuterJoin06

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 –

OuterJoin07

Now you will get all records from both tables whether they exist in the other or not.

OuterJoin08

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.