Hi - I have an Access 2000 database with twenty tables in it, all with identical structure but different data. I have to run a set of 4 queries on each table which are repeated for all 20 of the tables to fill and manipulate data in various fields and the repetition is driving me nuts, leads to a high risk of error and is just plaintime consuming - takes me around two days to do. and is repeated every few weeks.

I believe there is a way to join a set of queries in sql into one query so that they will run one after the other but can't find how. i.e make one looong sql query that will open each table in turn and do the necessary to each one.

Please please please help _ _ _ please, pretty please. - you can tell I'm desperate.

The reason for the many identical tables is that my GIS program requires it for each feature.

Why not create a form with a button on it. In side the button get it to call each query.

(haven't done access for years otherwise i'd be far more helpful)

To get a clue how to do it, copy your database and create a form with two or three buttons on it. For each button attach the query you need ..... edit hte buttons to see what actual code is behind it - copy this code and you should see what the difference is between queries to make your big "one click" button.

Only issue is error trapping ...... errr .... hope it doesnt' fall over or someone more experienced answers

How come you have to run 4 queries for each table? Are you outputting 4 different sets of results for each table? I.E. you produce 80 sets of results across 20 tables? Seems ludicrous.

I'd build a proper Query to output all the information at once. And create the query to pull the info from the currently viewed table in form view.

What do you do with the query info at the end? Export it to another program?

If the table structures are identical, you might be able to use the UNION command to join them all together (Not sure if this is available on Access though)

Cheers

Scott

To Deezee & Scott

The queries build data and calculate further data based on the results of the previously built data eg
1st query - Field C = Field A X Filed B
2nd Query - Field E = Field C X Field D
3rd Query - Field H = Field M - Field E
4 th Query - Field J = Field P X Field M / Field Q

Scott - have tried "Union" but get message "Characters found after end of SQL statement" that I cannot find the meaning of.

To all three of you - I am sure the proper way to do this is in VBA but not sure how - will continue to research

Thanks for the feedback.



[Edited on 10/4/09 by Ivan]