Full Outer Join in MS Access I have two staff lists:

List A:

StaffID   Supervisor
====================
0001234         NULL
0001235      0001234
0001237      0001234
0001239      0001237


and

List B:

StaffID   Supervisor
====================
0001234         NULL
0001235      0001234
0001238      0001235
0001239      0001235


And I need the following output:

StaffID   SupervisorA   SupervisorB
===================================
0001234          NULL          NULL
0001235       0001234       0001234
0001237       0001234          NULL
0001238          NULL       0001235
0001239       0001237       0001235


Notice that the staff IDs from List A and List B have been merged into a single, non-repeating list, and that I have merged the two supervisor details which may not match between the two lists.

Query doesn't have to be nice. I have ~8000 records in each list and I will run it nice. I'm happy to do some minor Excel manipulation afterwards if necessary.

I wanted to do a full join, but the Access query designer properties window on the join allows me to choose ALL from table A and matching from table B, ALL from table B and matching from table A, or only those which match in both A and B.

I'm sure this is very simple to do, but I very rarely use MS Access.

I have two queries so far which don't give me what I want. I wanted to combine them in the full outer join, since Access won't give me an option, but not sure how:

SELECT ListA.*, ListB.*
FROM ListA LEFT JOIN ListB ON ListA.[StaffID] = ListB.StaffID;


and

SELECT ListA.*, ListB.*
FROM ListB LEFT JOIN ListA ON ListB.[StaffID] = ListA.StaffID;
The last time I played with Access was when 2003 was the hot new thing, so this may not be entirely accurate to every detail. However, what you need to do is go to the query designer, change the view to "SQL" (i.e. raw text entry) and then you want to UNION your two left-join queries together, e.g.

SELECT ListA.*, ListB.*
FROM ListA LEFT JOIN ListB ON ListA.[StaffID] = ListB.StaffID
UNION
SELECT ListA.*, ListB.*
FROM ListB LEFT JOIN ListA ON ListB.[StaffID] = ListA.StaffID