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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Query 1
APPENDING TO A TABLE WITH DATA 7
Not able to use append query 4
Append Queries in 2007 5
Append query bloat 4
Importing text file with append 1
Append Query 3
Append Query Problem 1

Back
Top