How to Create a Full Outer Join in Access

At times, you may need to create a full outer join in Access. Yet, one of the limitations of Access is that you cannot create such a join.

Well at least not directly…

You can create a full outer join by ‘tricking’ the system.

And what do I mean by saying ‘tricking the system’?

You can separately apply Left and Right Joins. Then, you can use Union to get your desired full outer join.

Here is the general template that you may use to create your full outer join in Access:

 

SELECT 
*
FROM Table_1
LEFT JOIN Table_2 
ON Table_2.ID = Table_1.ID

UNION

SELECT 
* 
FROM Table_1
RIGHT JOIN Table_2 
ON Table_2.ID = Table_1.ID

 

In the next section, I’ll use an example to show you how to create a full outer join in Access.

The Example

Let’s say that you have two tables in Access:

  • Client_First_Name that contains a list of first names
  • Client_Last_Name which contains a list of last names

The Client_First_Name table would look like this:

 
Client IDClient First Name
1Jon
2Maria
3Bill
4Mark
5Jill

 

While the Client_Last_Name table would look like this:

 
Client IDClient Last Name
1Smith
2Jones
3Brown
6Wilson
7Martin

 

You’ll notice that each of the tables contains a ‘Client ID‘ field. The goal here is to connect the two tables using that Client ID field.

You will also notice that some Client IDs exist in the first table, but not in the second table (and vice versa).

This is when a full outer join becomes useful. It will allow you to get ALL the records from both tables.

Let’s now look at the steps to create a full outer join using the above example.

Steps to create a full outer join in Access

(1) First thing first, create the above two tables in Access.

Save the first table as Client_First_Name:

 

Left Table Access

 

And save the second table as Client_Last_Name:

 

Right table Access

 

(2) Now create the Left Join portion of the query:

 

SELECT 
[Client_First_Name].[Client ID], 
[Client_First_Name].[Client First Name], 
[Client_Last_Name].[Client Last Name]
FROM Client_First_Name 
LEFT JOIN Client_Last_Name 
ON [Client_Last_Name].[Client ID]=[Client_First_Name].[Client ID]

 

(3) Then, create the Right Join portion of the query:

 

SELECT 
[Client_Last_Name].[Client ID], 
[Client_First_Name].[Client First Name], 
[Client_Last_Name].[Client Last Name]
FROM Client_First_Name 
RIGHT JOIN Client_Last_Name 
ON [Client_Last_Name].[Client ID]=[Client_First_Name].[Client ID]

 

(4) Finally, to create your full outer join, place a ‘UNION‘ in between the Left Join portion and the Right Join portion:

 

SELECT 
[Client_First_Name].[Client ID], 
[Client_First_Name].[Client First Name], 
[Client_Last_Name].[Client Last Name]
FROM Client_First_Name 
LEFT JOIN Client_Last_Name 
ON [Client_Last_Name].[Client ID]=[Client_First_Name].[Client ID]

UNION

SELECT 
[Client_Last_Name].[Client ID], 
[Client_First_Name].[Client First Name], 
[Client_Last_Name].[Client Last Name]
FROM Client_First_Name 
RIGHT JOIN Client_Last_Name 
ON [Client_Last_Name].[Client ID]=[Client_First_Name].[Client ID]

 

Ta-da! you just created your full outer join in Access!

All the records from both tables would now appear (even if the Client ID key does not exist in both of the tables).

That way, you’ll make sure that all of your records are fully captured.

 

How to Create a Full Outer Join in Access

 

In our example, you can observe that some values are missing under both the Client First and Last Names. By using a full outer join, you can recognize those missing values, and then add them in the appropriate tables.

That’s it for this tutorial! You may wish to check the following source for additional tutorials on MS Access.