Combine 5 tables into one table (all in TABLE tab)

[Guest]

I have 5 tables in my Access-Table Tab.

What's the best way to combined all the 5 tables and become one single
table. Note all 5 tables have similar columns numbers and names.

Intend to use Macro -- meaning one button to do the job.

Appreciate your thoughts.

Thanks

 

[Douglas J. Steele]

Not sure I understand why you'd want a macro for this: is it something
you're going to need to do often? If so, perhaps you might want to
reconsider your table design.

In any case, the normal approach for combining tables that have similar
fields would be to use a UNION query:

SELECT Field1, Field2 FROM Table1
UNION
SELECT Field1, Field2 FROM Table2
UNION
....
UNION
SELECT Field1, Field2 FROM Table5

If you need to know what table each entry came from originally, you could
use something like:

SELECT "Table1" AS Source, Field1, Field2 FROM Table1
UNION
SELECT "Table2" AS Source, Field1, Field2 FROM Table2
UNION
....
UNION
SELECT "Table5" AS Source, Field1, Field2 FROM Table5