unifying two tables

[Guest]

i just can't seem to find the way to do something simple. 2 tables in access.
each table has 2 fields. lets say table one has name and weight for its
fields and table 2 has name and height for its fields. the name fields are
linked under relationships of course. in each table each name only appears
once. the two tables overlap many names, one table might even be a subset of
the other but they are NOT the same. so some of the names don't have weight
and some don't have a weight. i want access to create a new table listing ALL
the names from the two tables, one name per record, but 2 fields: the weight
(if it exists in table 1 or else blank) and the height (if it exists in table
2, or else blank). can someone help??? thank you........

 

[Lynn Trapp]

This is untested, but may give you a good start.

Create Table YourTable
AS
(Select A.ProductName, A.Weight, B.Height
From WeightTable AS A Join HeightTable AS B
On A.Name = B.Name)

 

[Douglas J. Steele]

SELECT Table1.[Name], Table1.Weight, Table2.Height
FROM Table1 INNER JOIN Table2
ON Table1.[Name] = Table2.[Name]
UNION
SELECT Table1.[Name], Table1.Weight, ''
FROM Table1 LEFT JOIN Table2
ON Table1.[Name] = Table2.[Name]
WHERE Table2.Height IS NULL
UNION
SELECT Table2.[Name], '', Table2.Height
FROM Table2 LEFT JOIN Table1
ON Table1.[Name] = Table2.[Name]
WHERE Table1.Weight = Null

or

SELECT Table1.[Name], Table1.Weight, Nz(Table2.Height, '')
FROM Table1 LEFT JOIN Table2
ON Table1.[Name] = Table2.[Name]
UNION
SELECT Table2.[Name], Nz(Table1.Weight, ''), Table2.Height
FROM Table2 LEFT JOIN Table1
ON Table1.[Name] = Table2.[Name]