combining table via query

T

tang lk

If i have 2 tables as follow:

Table1
Name Id
John 10
Mary 11
William 13



Table2
Name 2ndId
John B2
Mary B1
Peter A3

I would like to have a join result as follow:

Name Id 2ndId
John 10 B2
Mary 11 B1
William 13
Peter A3

How to do it in Access Query?

regards
 
M

Michel Walsh

Hi,


SELECT x.Name, a.Id, b.2ndId
FROM ((SELECT Name FROM Table1 UNION
SELECT Name FROM table2) AS x
LEFT JOIN table1 As a
ON a.name = x.name
) LEFT JOIN table2 as b
ON b.name = x.name



The trick is based on getting a list with all the possible Name (the UNION
query) and from there, it is a matter to make two outer joins.


Hoping it may help,
Vanderghast, Access MVP
 
Top