Merging 2 Tables

D

Doug Fete

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
 
G

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
 
D

Doug Fete

Giorgio,

Thanks for the help!!

Doug

giorgio rancati said:
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

Doug Fete said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List all tables, including document structure 0
Filling in some blanks to a data table 3
Format column widths from one table to a separate table on same page 1
Clicking link tasks again increases duration 0
List of Direct and Indirect Reports 7
Acronym Macro 11
A Macro to Insert Rows 1
Subquery not integrating to query to display previous sum of quantities 3

Top