need to merge 2 tables joined on a field

[Muppet]

I have 2 tables:

-- Table1 -------------
Color Price Height
-----------------------
Green 1 10
Red 2 20
Orange 3 30

-- Table2 -------------
Color Length Width
-----------------------
Blue 100 1000
Red 200 2000
Green 300 3000

I want to 'merge' values joined on Color. The values need to be
merged based on color and the Amounts need to be in separate columns. The
final result should look like:

Color Price Height Length Width
--------------------------------------
Green 1 10 300 3000
Red 2 20 200 2000
Orange 3 30
Blue 100 1000


Some things can be depended on:

- Table1 might have colors that are not in Table2 and vice versa.
- Colors within a table will never be duplicated (although both tables may
contain the same colors).
- The only common field is Colors.

I have been able to get as far as the first query to merge Table2 contents
with Table1:
SELECT Tbl1.*, Tbl2.Length, Tbl2.Width
FROM Tbl1 LEFT JOIN Tbl2 ON Tbl1.Color = Tbl2.Color;

but then I'm stuck.

Thanks for any suggestions.

 

[Michel Walsh]

Hi,


make a table Colors, one field, color, with all the possible values of
color. That would also be useful to oblige your user to enter real color (no
Reed instead of Red, and so on) in table1 and table2.

SELECT... FROM (colors LEFT JOIN table1 ON colors.color = table1.color) LEFT
JOIN table2 ON colors.color=table2.color



Hoping it may help,
Vanderghast, Access MVP

 

[Muppet]

Thanks for responding. What you gave me is close but not quite what I need:

SELECT table1.*, [table2].[Length], [table2].[Width]
FROM (colors LEFT JOIN table1 ON [colors].[color]=[table1].[Color]) LEFT
JOIN table2 ON [colors].[color]=[table2].[Color];

The result is:

----------------------------------------------
Color Price Height Length Width
Green 1 10 300 3000
Red 2 20 200 2000
Orange 3 30
100 1000
----------------------------------------------

But I need:
----------------------------------------------
Color Price Height Length Width
Green 1 10 300 3000
Red 2 20 200 2000
Orange 3 30
Blue 100 1000

 

[Muppet]

Ok, now I see what you meant. By "...", you must have meant: "colors.color".
For completion, this seems to work:

SELECT colors.color, Table1.Price, Table1.Height, Table2.Length,
Table2.Width
FROM (colors LEFT JOIN Table1 ON colors.color = Table1.Color) LEFT JOIN
Table2 ON colors.color = Table2.Color;

Thanks for responding. What you gave me is close but not quite what I need:

SELECT table1.*, [table2].[Length], [table2].[Width]
FROM (colors LEFT JOIN table1 ON [colors].[color]=[table1].[Color]) LEFT
JOIN table2 ON [colors].[color]=[table2].[Color];

The result is:

----------------------------------------------
Color Price Height Length Width
Green 1 10 300 3000
Red 2 20 200 2000
Orange 3 30
100 1000
----------------------------------------------

But I need:
----------------------------------------------
Color Price Height Length Width
Green 1 10 300 3000
Red 2 20 200 2000
Orange 3 30
Blue 100 1000
----------------------------------------------

with Blue in the last row of the first column.

Hi,


make a table Colors, one field, color, with all the possible values of
color. That would also be useful to oblige your user to enter real color (no
Reed instead of Red, and so on) in table1 and table2.

SELECT... FROM (colors LEFT JOIN table1 ON colors.color = table1.color) LEFT
JOIN table2 ON colors.color=table2.color



Hoping it may help,
Vanderghast, Access MVP