Using MS Access/Outer Join



I have two tables with more or less identical structures, but with overlapping data. I would like to create a query such that I can easily see all the differences in the two tables. I *think* what I need/want is an outer join, which--as you know--Access doesn't permit except through SQL statements. I'm hoping you can you help me write that SQL query.

Here is what the output from my query might look like.

         ( From Table 1 )          ( From Table 2 )
Cust         Amt$     ZIP     Type      Amt$    ZIP      Type
------       -----   -----    ------    ----   -----     -------
Barnes       100     93401    apples    200    93401     oranges
Smith          300    91209     walnuts
Peters       500     92722    peaches
Clark        250     91367    limes     250    91367     limes   

Barnes had different data in both tables; Smith had data in table 2 but none in table 1; Peters had data in table 1 but not in table 2; Clark had identical data in both tables.

I'm not sure if this is enough info, but neither do I want to send too much. So we'll start with this; please ask if you want more.

Anything you can do or suggest to help me accomplish my objective would be appreciated.

ANSWER: Access most certainly DOES allow outer joins in the IDE. On the join line in your query, double-click on it, and you'll see options for an outer join (show all records from table 1 and related records in table 2). Once you've created that join, run the query and simply use criteria to show which values are different in the fields you want to compare.

I cover outer joins in my Access Expert Level 1 class.

Be sure to visit my web site and watch my FREE 3-hour-long Microsoft Access Video Tutorial

Richard Rost
Microsoft Access MVP
Computer Learning Zone

---------- FOLLOW-UP ----------

QUESTION: Hi Richard--

Thanks for your reply.

But one of us is misunderstanding the meaning "outer join." In your reply to me, you say "... outer join (show all records from table 1 and related records in table 2)." That's not what I want. I want ALL records in table 2 to show, not just those that match some field in table 1. In my example above, how is the "Smith" row going to show up in the query output if there is no "Smith" row in table 1?

There's a LEFT JOIN and a RIGHT JOIN. Both are OUTER joins. It all depends on which table you want to see all records from. Again, these are both covered in my lessons, and you can get to them by double-clicking on the join line and selecting which option you want.

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Richard Rost


I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site, and free Access 101 tutorial.


I am the president of and I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at

©2016 All rights reserved.