MCALLEN3388 MCALLEN3388 - 5 months ago 14
SQL Question

?? Combine, join, insert in query Access 2007-2010?

I have two tables that I would like to make into one table.

TABLE WASP_COLOR1

ID NAME COLOR CODE
1 WASP01 RED RD
2 WASP04 RED RD
3 WASP19 BLUE BL


TABLE WASP_COLOR2

ID NAME SIZE CODE
1 WASP01 6 RD
2 WASP13 10 BL
3 WASP22 4 BL


I'm a novice and I need clear direction on how to obtain these results. (I want the duplicates merged.)

TABLE WASP_COLOR1

ID NAME COLOR SIZE CODE
1 WASP01 RED 6 RD
2 WASP04 RED RD
3 WASP19 BLUE BL
4 WASP13 10 BL
5 WASP22 4 BL


I tried the following:

SELECT
distinct wasp_color2.name, wasp_color2.size,
wasp_color2.code, wasp_color1.name, wasp_color1.color,
wasp_color1.code
INTO
wasp_color1
FROM
wasp_color2, wasp_color1;


Received error

I tried:

SELECT
distinct wasp_color2.name, wasp_color2.size, wasp_color2.code,
wasp_color1.name, wasp_color1.color, wasp_color1.code
INTO
test
FROM
wasp_color2, wasp_color1;


Received 9 rows instead of 5

I tried:

SELECT
name.wasp_color1, name.wasp_color2, color, size,
code.wasp_color1, code.wasp_color2
INTO
test
FROM wasp_color2, wasp_color1;


This asks for a parameter value, which is a manual entry and cumbersome

I tried:

SELECT
coalesce name.wasp_color1, name.wasp_color2, color,
size, code.wasp_color1, code.wasp_color2
INTO
test
FROM
wasp_color2, wasp_color1 full join;


Received error

Answer

Perhaps something like this as MS access doesn't support full outer joins.

What this does is generate two data sets one for wasp_Color1 and those records that match in wasp_Color2 and vice-versa. and then combine's those results into one data set eliminating the duplicates.

For a better understanding of joins: see https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Select WC1.Name, WC1.Color, WC2.Size, WC1.Code
FROM wasp_Color1 wc1
LEFT JOIN wasp_color2 wc2
 on wc1.Name = WC2.Name
and wc1.code = wc2.code

UNION

Select WC2.Name, WC1.Color, WC2.Size, WC2.Code
FROM wasp_Color1 wc1
RIGHT JOIN wasp_color2 wc2
 on wc1.Name = WC2.Name
and wc1.code = wc2.code
Comments