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