Simply Merging ten tables

[Mike C]

I have ten tables that I want to merge into a single table. As of now,
they do not have a unique primary key.

They have the same field names.

There are not any duplicates, so the end result will simply be a
single table containing all rows from all tables.

Do I need to run ten separate append queries, or is there a simpler
way to merge them all in a single step?

Thanks.

(also, is there a quick way to import ten tables, rather than
importing them one at a time?)

 

[John W. Vinson]

I have ten tables that I want to merge into a single table. As of now,
they do not have a unique primary key.

They have the same field names.

There are not any duplicates, so the end result will simply be a
single table containing all rows from all tables.

Do I need to run ten separate append queries, or is there a simpler
way to merge them all in a single step?

It's perhaps a tossup: an append query based on a rather complex UNION query,
or ten append queries. If this is a recurring task I'd go with the UNION.

To create a UNION query you need to use the SQL window. I'd start with a
simple select query selecting all the fields in one of your tables. Go into
SQL view and you'll see something like

SELECT Table1.Field1, Table1.Field2, Table1.Field3, ... FROM Table1;

Edit this (you can copy and paste) to

SELECT Table1.Field1, Table1.Field2, Table1.Field3, ... FROM Table1;
UNION ALL
SELECT Table2.Field1, Table2.Field2, Table2.Field3, ... FROM Table21;
UNION ALL
SELECT Table3.Field1, Table3.Field2, Table3.Field3, ... FROM Table3;
UNION ALL
<etc for all ten tables>

You can then base an Append query on this UNION query.

Thanks.

(also, is there a quick way to import ten tables, rather than
importing them one at a time?)

You can write VBA code using the TransferDatabase method to run all the
imports in one operation... but it's still going to be ten imports. It's just
a matter of having the program run them vs. the user doing so.