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;