Hi. I have a task that should be easy with an Access query.
I have 2 table (imported from Excel), with a common field (MYCODE), while the other fields are different.
I made a simple relationship between the two tables.
Table1
MYCODE field1 field2
AAA a a
BBB b b
CCC c c
Table2
MYCODE field3 field4 field5
BBB b b b
DDD d d d
EEE e e e
I want merge data into NEW_TABLE
Final table must have all rows, see below:
MYCODE field1 field2 field3 field4 field5
AAA a a - - -
BBB b b b b b
CCC c c - - -
DDD - - d d d
EEE - - e e e
I am trying with UNION Query, such as
SELECT MYCODE INTO NEW_TABLE
FROM (SELECT MYCODE from Table1
UNION
SELECT MYCODE from Table2)
This creates the 5 row (this is correct), but how to include all fields from the tables in the SQL?
when I try it in design I see a JOIN is added automatically
SELECT Table1.MYCODE, Table1.field1, Table1.field2, Table2.field3, Table2.field4, Table2.field5 INTO NEW_TABLE
FROM Table1 inner JOIN Table2 ON Table1.MYCODE = Table2.MYCODE;
with this JOIN not all the rows are inserted...
Not sure hoe to go on...
Thanks for your suggestions
F