Easier way to append lots of data

[Guest]

I have 100 tables in an Access database (2 tables for each state). The tables
are called tblAK1, tblAK2, tblVA1, tblVA2 etc.

Each table has two fields a counter field and a field called STUDENTS

I would like to create a table called tblUS and append the data from the 100
tables into this one. I am not concerned about the Counter field but would
like to get the data from the STUDENTS field. Students is a text field.

I usually append one table at a time by using an append query. Is there an
easier and less time consuming way to do it. (I hope that there is an easier
way!)

Please help!

 

[Guest]

100 tables for each that contain the same data? Whoa, why not one table with
a state field? Anyhow, create and save your append queries. Use VBA to
iterate through the queries...

 

[Guest]

All the tables came to me this way. Thanks for your help xRoachx. I really
appreciate your input.

 

[Guest]

No problem. If you need help with the VBA, post back and I'm sure someone,
if not me, will be able to help you. Good luck.

 

[John Vinson]

I have 100 tables in an Access database (2 tables for each state). The tables
are called tblAK1, tblAK2, tblVA1, tblVA2 etc.

Each table has two fields a counter field and a field called STUDENTS

I would like to create a table called tblUS and append the data from the 100
tables into this one. I am not concerned about the Counter field but would
like to get the data from the STUDENTS field. Students is a text field.

I usually append one table at a time by using an append query. Is there an
easier and less time consuming way to do it. (I hope that there is an easier
way!)

Please help!

Just an additional suggestion in addition to xRoachx's: you can use a
UNION query to do these in batches. I doubt you can do 100 in one
swell foop but I'm sure you could do a third of them at a time.

Use a text editor (with better copy&paste features than the VBA editor
window) to create a UNION query such as

SELECT tblAK1.STUDENTS FROM tblAK1
UNION ALL
SELECT tblAK2.STUDENTS FROM tblAK2
UNION ALL
SELECT tblVA1.STUDENTS FROM tblVA1
UNION ALL
<etc etc>

Try the first fifty and see if that works.

Then base an Append query upon this stored UNION query.

John W. Vinson[MVP]