Merging 2 Tables

[Guest]

Hi,

I am looking to combine two tables. Both tables have one column with the
same name but all the other columns are different. The column with the same
name, "Tags", may have the same row value in both tables, but there are
different values also. I am looking to add both tables - if the value for
"Tags" is the same, I would like to combine the two table records. If there
is a unique value in either database, I would like to add that record.

Short ex:
First table
TAGS Column 1 Column 2
AAAA 5 10
BBBB 1 3
DDDD 4 8
EEEE 9 25

Second Table
TAGS Column A Column B
AAAA 17 25
BBBB 11 22
CCCC 5 1

The combined table would look like this:
TAGS Column 1 Column 2 Column A Column B
AAAA 5 10 17 25
BBBB 1 3 11 22
CCCC 0 0 5 1
DDDD 4 8 0 0
EEEE 9 25 0 0

Thanks for any help,
Doug

 

[giorgio rancati]

Hi Doug,

You need a FULL JOIN , unfortunately it does not exist in Access.
You can resolve with a LEFT JOIN in UNION with an RIGHT JOIN.

----
SELECT IIF(IsNull (A.TAGS),B.Tags,A.TAGS) AS TAGS,
[Column 1] AS [Col 1],
[Column 2] AS [Col 2],
IIF(ISNULL([Column A]),0,[Column A]) AS [Col A],
IIF(ISNULL([Column B]),0,[Column B]) AS [Col B]
FROM [FIRST TABLE] AS A LEFT JOIN [Second Table] AS B ON A.TAGS=B.TAGS
UNION
SELECT IIF(IsNull (A.TAGS),B.Tags,A.TAGS),
IIF(ISNULL([Column 1]),0,[Column 1]) ,
IIF(ISNULL([Column 2]),0,[Column 2]),
[Column A],
[Column B]
FROM [FIRST TABLE] AS A RIGHT JOIN [Second Table] AS B ON A.TAGS=B.TAGS

 

[Guest]

Giorgio,

Thanks for the help!!

Doug

Hi Doug,

You need a FULL JOIN , unfortunately it does not exist in Access.
You can resolve with a LEFT JOIN in UNION with an RIGHT JOIN.

----
SELECT IIF(IsNull (A.TAGS),B.Tags,A.TAGS) AS TAGS,
[Column 1] AS [Col 1],
[Column 2] AS [Col 2],
IIF(ISNULL([Column A]),0,[Column A]) AS [Col A],
IIF(ISNULL([Column B]),0,[Column B]) AS [Col B]
FROM [FIRST TABLE] AS A LEFT JOIN [Second Table] AS B ON A.TAGS=B.TAGS
UNION
SELECT IIF(IsNull (A.TAGS),B.Tags,A.TAGS),
IIF(ISNULL([Column 1]),0,[Column 1]) ,
IIF(ISNULL([Column 2]),0,[Column 2]),
[Column A],
[Column B]
FROM [FIRST TABLE] AS A RIGHT JOIN [Second Table] AS B ON A.TAGS=B.TAGS
----

Bye Giorgio

Hi,

I am looking to combine two tables. Both tables have one column with the
same name but all the other columns are different. The column with the same
name, "Tags", may have the same row value in both tables, but there are
different values also. I am looking to add both tables - if the value for
"Tags" is the same, I would like to combine the two table records. If there
is a unique value in either database, I would like to add that record.

Short ex:
First table
TAGS Column 1 Column 2
AAAA 5 10
BBBB 1 3
DDDD 4 8
EEEE 9 25

Second Table
TAGS Column A Column B
AAAA 17 25
BBBB 11 22
CCCC 5 1

The combined table would look like this:
TAGS Column 1 Column 2 Column A Column B
AAAA 5 10 17 25
BBBB 1 3 11 22
CCCC 0 0 5 1
DDDD 4 8 0 0
EEEE 9 25 0 0

Thanks for any help,
Doug