Append multiple Access tables to one with one query

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I have 130 small tables I would like to append to a master
table. Is there a way I can do this in one append query?
 
I have 130 small tables I would like to append to a master
table. Is there a way I can do this in one append query?
Click to expand...

No, but probably three or four rather than 130. Create a UNION query:

SELECT field, field, field FROM Table1
UNION ALL
SELECT field, field, field FROM Table2
UNION ALL
<etc etc>

You can build this in the SQL window using copy and paste, if all the
tables have the same fieldnames it can go pretty quickly. If you can
generate this SQL programmatically so much the better!

Try opening the query every ten or twenty tables - you'll eventually
get a QUERY TOO COMPLEX error; when you do, remove the last two or
three tables.

Now save the query and create an Append query based on the saved UNION
query.

Repeat the procedure starting where you left off.
 
Back
Top