Microsoft Access Outer Join Query: Finding All Records in One Table but Not Another and Creating "Not In" Queries

Assume that we want a list of all customers, and if they have any Orders, we want to show the order dates. In this scenario, we want to see customers with orders AND customers without orders. We can use a Left Outer Join to accomplish this.

---

Designing the Query

Follow these steps to design the query interactively (or paste the Query SQL instead):

1. In the Designer Window, add the Customers and the Orders tables. The Join line is automatically added to join [Customers].[ID] to [Orders].[Customer ID].
2. Double click on the line to open the Join properties
   
   
   Notice that this shows the name of the Left table and the Right table, and lets you choose your Join option. In this case, we want option #2: “Include ALL records from ‘Customers’ and only those records from ‘Orders’ where the joined fields are equal.”
3. Click OK to return to the Designer Window, and add the fields [Customers].[Company] and [Orders].[OrderDate]

The designer window should look like this:

---

Query SQL

The SQL statement for the query is as follows:

SELECT Customers.Company, 
Orders.[Order Date]
FROM Customers
LEFT JOIN Orders ON Customers.ID = Orders.[Customer ID]

---

Query Results

Run the query, and you see all companies and their orders, if any. If the company has no associated order, it still shows in the list, only with a Null Order Date.

If we used an Inner Join, then Company B would be excluded from the results, since there is no matching record in the Orders table.

Now assume that we ONLY want to find the customers who have not placed any orders. Just like in the first scenario, we will use a Left Outer Join to accomplish this. This time, however, we’ll add criteria to exclude the rows that have orders.

---

Designing the Query

In the Designer Window, add the Customers and the Orders table, and set the Join type to be a Left Outer Join (as explained in Steps 1 and 2 above).

In the Designer Window, and add the following fields:

The designer window should look like this:

---

Query SQL

The SQL statement for the query is as follows:

SELECT Customers.CompanyFROM Customers
LEFT JOIN Orders ON Customers.ID = Orders.[Customer ID]
WHERE (((Orders.[Order ID]) Is Null))

---

Query Results

Run the query, and you see only the companies who have no associated orders:

Now assume now that we want to find customers who have not placed orders in the last year. The steps are similar to finding records that exist in one table, but not another, except that rather than joining the Customers table to the Orders table, we join it to a saved query that returns Orders within the last year.

First save the following query as "qryOrdersLastYear":

SELECT * FROM Orders WHERE ((([Order Date])>=DateAdd('yyyy',-1,Date())))

And follow the steps above, only using qryOrdersLastYear instead of Orders. The SQL statement for the query is as follows:

SELECT Customers.Company
FROM Customers
LEFT JOIN qryOrdersLastYear ON Customers.ID = qryOrdersLastYear.[Customer ID]
WHERE (((qryOrdersLastYear.[Order ID]) Is Null))

Outer joins also come in handy when we want to troubleshoot missing data. For instance, if we find ourselves with orphaned Order rows, we can find which orders don’t have an associated customer.

---

Designing the Query

Follow these steps to design the query interactively (or alternatively, you can paste the Query SQL below)

1. In the Designer Window, add the Customers and the Orders table. The Join line is automatically added to join [Customers].[ID] to [Orders].[Customer ID].
2. Double click on this line to open the Join properties:
   

Notice that this shows you the name of the Left table and the Right table, and lets you choose your Join option. In this case, we want option #3: "Include ALL records from ‘Orders’ and only those records from 'Customers' where the joined fields are equal." This is a Right Outer Join, since the Orders table is our Right table.

Click OK to return to the Designer Window, and add the following fields:

The designer window should look like this:

---

Query SQL for Missing or Orphaned Records

The SQL statement for the query is as follows:

SELECT Orders.[Order ID]
FROM Customers
RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID]
WHERE (((Customers.ID) Is Null))

---

Query Results

Run the query, and you see any orphaned Orders records (orders that have a non-existent customer). For instance, if Customer 1 is missing from the Customers table, the results are:

(Note that Referential Integrity was removed from the relationship for the purpose of this example. Enforce Referential Integrity to prevent orphaned records. See http://support.microsoft.com/kb/304466 for details.)

We can also use Outer Joins to find rows that are missing between tables. Assume that we have two copies of the Customers table, but records were added and deleted from both tables. We need to see see the records from both tables that aren't in the other.

To see rows in Customers that are not in Customers1, the Query Designer looks like:

and the SQL is:

SELECT Customers.ID, Customers.Company
FROM Customers
LEFT JOIN Customers1 ON Customers.ID = Customers1.ID
WHERE (((Customers1.ID) Is Null))

To see the opposite (rows in Customer1 that are not in Customers), the designer looks like:

and the SQL is

SELECT Customers1.ID, Customers1.Company
FROM Customers
RIGHT JOIN Customers1 ON Customers.ID = Customers1.ID
WHERE (((Customers.ID) Is Null))

Total Access Emailer 2022
Email Everyone in Your Access List!

---

Total Visual Agent 2021
Automate Access Database Chores!

---

Updated Microsoft Access to SQL Server Upsizing Center with whitepapers, resources, and SQL Server Express Downloads