Ivan
|
| posted on 10/4/09 at 08:57 AM |
|
|
MS Access help needed
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.
|
|
|
|
|
MikeR
|
| posted on 10/4/09 at 09:17 AM |
|
|
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 
|
|
|
deezee
|
| posted on 10/4/09 at 10:19 AM |
|
|
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?
|
|
|
phoenix70
|
| posted on 10/4/09 at 11:11 AM |
|
|
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
|
|
|
Ivan
|
| posted on 10/4/09 at 02:20 PM |
|
|
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]
|
|
|
