Full Outer Join in MS Access

Question

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;

Answer 1

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

Answer 2

SELECT S.StaffId
     , ListA.Supervisor AS SupervisorA 
     , ListB.Supervisor AS SupervisorB
FROM 
    ( SELECT StaffID
      FROM ListA 
    UNION 
      SELECT StaffID
      FROM ListB
    ) AS S
  LEFT JOIN ListA 
    ON ListA.StaffID = S.StaffID
  LEFT JOIN ListB 
    ON ListB.StaffID = S.StaffID

Answer 3

Have you tried designing the query in SQL mode instead of using the designer view?

If you are using Access 2003/XP/2000/97 then take a look at this: http://www.techonthenet.com/access/queries/view_sql.php

If you are using Access 2007/2010 you can do this by clicking the arrow on the View button on the left of the ribbon. Then click SQL View.

Answer 4

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

Answer 5

SELECT ListA.staffID, ListA.Supervisor, ListB.Supervisor
FROM ListA LEFT JOIN ListB ON ListA.staffID = ListB.staffID
WHERE (((ListB.staffID) Is Null))
UNION
SELECT ListB.staffID, ListA.Supervisor, ListB.Supervisor
FROM ListA RIGHT JOIN ListB ON ListA.staffID = ListB.staffID
WHERE (((ListA.staffID) Is Null))
UNION
SELECT ListA.staffID, ListA.Supervisor, ListB.Supervisor
FROM ListA INNER JOIN ListB ON ListA.staffID = ListB.staffID