combining table via query

  • Thread starter Thread starter tang lk
  • Start date Start date
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
 
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
 
Back
Top