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
Be sure to visit my web site and watch my FREE
3-hour-long Microsoft Access Video Tutorial
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.