MS Access Question: how to merge multiple tables

HIROYUKI TAMURA
HIROYUKI TAMURA used Ask the Experts™
on
I would like to merge multiple tables in MS Access.
I have 13 tables with same data fields.
How do I append 13 tables to 1 table?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
What exactly you mean by merge....if the tables have same structure you could use APPEND query to use one table as the "base" and append the data from the other tables.

Author

Commented:
Hi John, thank you as always. Sorry,  I should have explained better.
Distinguished Expert 2017

Commented:
Use a single append query for each source table

OR

Create a union query that unions all the source tables and then create a single append query out of that to append all the data at once.

Personally, I would choose the first option since you can run into issues with unions if Access decides they have too many tables.  That wouldn't be a problem if the number of tables were fixed but it seems to be arbitrary so go with door #1

In either case, I would probably add an additional field that I populate with a number or the actual file name so I can tell which table each row actually came from.  This might be handy if you have to back something out.
Top Expert 2014
Commented:
You can start with this query.  It will give you a vertical view of your tables.  If you need to create/populate a new table, change the original (first) Select statement
Select * From(
  Select * From table1
  Union ALL
  Select * From table2
  Union ALL
  Select * From table3
  Union ALL
  Select * From table4
  Union ALL
  Select * From table5
  Union ALL
  Select * From table6
  Union ALL
  Select * From table7
  Union ALL
  Select * From table8
  Union ALL
  Select * From table9
  Union ALL
  Select * From table10
  Union ALL
  Select * From table11
  Union ALL
  Select * From table12
  Union ALL
  Select * From table13
  Union ALL
)

Open in new window

Author

Commented:
Thank you, All

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial