How do I do a FULL JOIN in Access...

[MethMath]

Hi, got stuck on a presumably simple matter (for an expert), but cannot find
any solution: How do I do a FULL JOIN in Access ? I have two tables, tblA and
tblB, with the following


tblA
************
ID DATA

1 AA
2 BB
3 CC
4 DD
5 EE
6 FF


tblB
************
ID DATA

2 BBBB
3 CCCC
4 DDDD
5 EEEE
6 FFFF
7 GGGG



and I want to construct an SQL query to produce the following FULL JOIN
output when making a JOIN on tblA.ID=tblB.ID:

tblA.DATA tblB.DATA
******** ********
AA -
BB BBBB
CC CCCC
DD DDDD
EE EEEE
FF FFFF
- GGGG

Would appreciate to get any clues as to how the SQL-string should be designed.

 

[Michel Walsh]

Hi,




Either:


SELECT a.*, b.*
FROM ( ( SELECT id FROM tblA UNION SELECT id FROM tblB) As x
LEFT JOIN tblA As a ON a.id=x.id )
LEFT JOIN tblB As b ON b.id=x.id



Either

SELECT a.data, b.data
FROM tblA As a LEFT JOIN tblB As b
ON a.id=b.id

UNION ALL

SELECT a.data b.data
FROM tblA AS a RIGHT JOIN tlbB as b
ON a.id = b.id
WHERE a.id IS NULL



The first table makes a kind of junction table collecting all the IDs, then,
make two outer join from it. The second solution use an UNION with the
second part being a NO MATCH query, to reintroduce the records the simple
first outer join would have missed.



Hoping it may help,
Vanderghast, Access MVP

 

[MethMath]

Thanks ! I tried your first suggestion and it works just fine. It's amazing
how many competent persons "out there" one can turn to in case of problems !

So, Michel, I really appreciate your help.