unifying two tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kayfam - 31 May 2007 19:20 GMT

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........

Signature

kayfam

Reply to this Message

Lynn Trapp - 31 May 2007 19:33 GMT

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)

Signature

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com

>i just can't seem to find the way to do something simple. 2 tables in
>access.
[quoted text clipped - 13 lines]
> table
> 2, or else blank). can someone help??? thank you........

Reply to this Message

Douglas J. Steele - 31 May 2007 19:34 GMT

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]

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>i just can't seem to find the way to do something simple. 2 tables in
>access.
[quoted text clipped - 13 lines]
> table
> 2, or else blank). can someone help??? thank you........

Reply to this Message