Microsoft Access - Merging Multiple Tables with Same Fields

Question

I am trying to figure out how to create a table that merges records from two tables with identical structure. Each table will have the following fields: a document_ID as the primary key, a documentNumber, a documentRevision, and a documentTitle. The only difference in the two tables is that each table holds a certain "Type" of document. The merged table should end up holding ALL documents from both tables.

I have tried to use an append query, but I ran into an issue where I couldn't figure out how to get the query to run when a record was inserted into each table. In my search online, I saw some things about "Joining", but I had no luck at understanding how to make it work.

Any help is appreciated!

table1 : Documents of Type 1
+---------------+--------------------+-----------------+-------------------+
|  Document ID  |   Document Number  |   Document Rev  |  Document Title   |
+---------------+--------------------+-----------------+-------------------+
|      1        |      GCD_111       |         -       |     Title GCD1    |
|      2        |      GCD_222       |         A       |     Title GCD2    |
|      3        |      GCD_333       |         B       |     Title GCD3    |
+---------------+--------------------+-----------------+-------------------+

table2 : Documents of Type 2
+---------------+--------------------+-----------------+-------------------+
|  Document ID  |   Document Number  |   Document Rev  |  Document Title   |
+---------------+--------------------+-----------------+-------------------+
|      4        |      TSR_111       |         -       |     Title TSR1    |
|      5        |      TSR_222       |         A       |     Title TSR2    |
|      6        |      TSR_333       |         B       |     Title TSR3    |
+---------------+--------------------+-----------------+-------------------+

Result Table: Documents of Type 1 and Type 2
+---------------+--------------------+-----------------+-------------------+
|  Document ID  |   Document Number  |   Document Rev  |  Document Title   |
+---------------+--------------------+-----------------+-------------------+
|      1        |      GCD_111       |         -       |     Title GCD1    |
|      2        |      GCD_222       |         A       |     Title GCD2    |
|      3        |      GCD_333       |         B       |     Title GCD3    |
|      4        |      TSR_111       |         -       |     Title TSR1    |
|      5        |      TSR_222       |         A       |     Title TSR2    |
|      6        |      TSR_333       |         B       |     Title TSR3    |
+---------------+--------------------+-----------------+-------------------+

How to ask a good SQL question. Sample data (what you have), expected result (what you want), what you have tried. — Andre
Create > Query Design > SQL > select document_id, document_number ... from table1 UNION ALL document_id, document_number ... from table2 > Run — zedfoxus
are you asking how to append to "Result table" all records from table1 and table2? If yes, you don't want a JOIN. A JOIN doesn't mean physically joining tables (it's used to querying records that are the result of two tables overlapping/intersecting each other). — stifin
Thanks for the suggestions. @zedfoxus, this is for a Access database, not a SQL database. — Brandon Stone
Yes @stifin, I am just wanting to append the two tables together. I tried an append query, but couldn't figure out how to have it update the query when a new record was added to either table 1 or table 2. — Brandon Stone

stifin stifin · Accepted Answer · 2021-03-06T16:22:09

You can break the problem down. First, which are the records in table1 and table2 not already in resulttable? When you have a query to do this, append the results to the resulttable.

I'll use a slight modification of your example, where one record is already in resulttable:

resulttable:
+-------------+-----------------+--------------+----------------+
| Document ID | Document Number | Document Rev | Document Title |
+-------------+-----------------+--------------+----------------+
|           1 | GCD_111         |            - | Title GCD1     |
+-------------+-----------------+--------------+----------------+

First, the query

INSERT INTO resulttable
SELECT a.[Document ID], a.[Document Number], a.[Document Rev], a.[Document Title]

FROM

(SELECT t1.[document id], t1.[Document Number], t1.[Document Rev], t1.[Document Title]
FROM table1 AS t1
LEFT JOIN resulttable AS rt on t1.[Document ID] = rt.[Document ID]
WHERE rt.[Document ID] IS NULL

UNION ALL

SELECT t1.[document id], t1.[Document Number], t1.[Document Rev], t1.[Document Title]
FROM table2 AS t1
LEFT JOIN resulttable as rt on t1.[Document ID] = rt.[Document ID]
WHERE rt.[Document ID] IS NULL) AS a

Explanation: records in table1 and table2 not already in resulttable?

First, do this for table1. We can do the same for table2 later.

As you mentioned, we can use a JOIN. I used a LEFT JOIN here to work out where the two tables do NOT interect i.e. look at all table1 records and show me which ones are not already in resulttable. If you select all records from table1 and the matching resulttable records (intersecting on [Document ID], you'll get a better idea of how this works:

select t1.*, rt.*
from table1 as t1
left join resulttable as rt on t1.[Document ID] = rt.[Document ID]

Because we want the parts that do NOT match, we want the null bits. So, we can modify the last query by specifying WHERE resulttable records are null and specifying the column names we want. This will make the results look exactly how we want them to look for the ultimate INSERT. That is:

select t1.[Document ID], t1.[Document Number], t1.[Document Rev], t1.[Document Title]
from table1 as t1
left join resulttable as rt on t1.[Document ID] = rt.[Document ID]
WHERE rt.[Document ID] IS NULL

Next, UNION ALL to also get the "new" results from table2. (See the final query for where this UNION ALL is.)

Then I wrapped the SELECT.. UNION ALL SELECT.. in parentheses and gave it the alias "a". This allows me to work with that whole group inside the parentheses as if it was a table called "a".

After that, the rest is just a regular INSERT statement.

Microsoft Access - Merging Multiple Tables with Same Fields

1 Answers