Do you mean you want a FULL OUTER JOIN? That is you have two tables that
should be joined on one or more fields. You are right Access doesn't
directly support this.
To emulate a Full Outer Join, you need to union a Left outer join with a
"frustrated" Right outer join:
SELECT somefields
FROM tb1 LEFT JOIN tb2 ON tb1.f1 = tb2.fA
UNION
SELECT somefields
FROM tb1 RIGHT JOIN tb2 ON tb1.f1 = tb2.fA
WHERE tb1.f1 IS NULL
============================================================================
============================================================================
Michel Walsh (MVP) posted this example.
Here are two other alternatives:
SELECT x.id FROM x
UNION
SELECT y.id FROM y
saved as qu1. Then
SELECT x.*, y.*
FROM ( qu1 LEFT JOIN x ON qu1.id=x.id)
LEFT JOIN y ON qu1.id=y.id
will do your full outer join (on id).
Another solution is to use the union after the outer join:
SELECT x.id, y.id
FROM x LEFT JOIN y ON x.id=y.id
UNION
SELECT x.id, y.id
FROM x RIGHT JOIN y ON x.id=y.id
WHERE x.id IS NULL
You recognize the right join as finding records in x not in y, which
complete the first left outer join. Indeed, you have to NOT include those
already included by the first LEFT JOIN and an UNION ALL won't do the trick
since it is possible that the initial tables both have duplicated record (a
UNION ALL would incorrectly remove those dup). So, the WHERE x.id IS NULL is
really important, in general.