running queries from a macro

  • Thread starter Thread starter LynMVM
  • Start date Start date
L

LynMVM

I am definitely not a programmer and have never used macro in access before.
I have a database that has grown way beyond my expectations when I set it up.
I believe it is a bad idea to have calculated fields in a database so I
originally decided to write some queries which I would run each time someone
wanted data off the database.
But as I am not always available and the number of variables they want has
grown I have ended up with a successions of 14 queries, a succession 0f 13
queries and a set of 5 queries all of which could need to be run after new
data is added and before data is retrieved.
Some probably could have been combined if I understood why they often gave
the wrong answers when I tried to do more than one calculation per query. BUT
they all work reliably now so can I write a macro to run them all in order so
I can get the clerk to run them each time new data is added.
while looking for help I read about one query not finishing before the next
started; could that happen? because that would be disastrous.
 
Lyn,

Can you say please what sort of queries they are? Append queries, Update
queries, Make-table queries...?
 
I would think that issuing a RunQuery would result in the Macro waiting until
that statement was 100% complete prior to the next RunQuery statement being
executed.

I haved used this technique numerous times, and have never had an issue.
 
Hi Steve,
Sorry for the delay, as often happens I was put on another job, and the
databases have been neglected and so I have come back to the same problem
again.
I have all the calculated variables in tables separate from the original
data input tables and the fields are all filled by a series of Update queries
(in one case a series of 14 update queries). The number is so high because I
never found a way of calculating the average of between one and 3 values in 3
columns for each row So the way I solved it was with queries like if x and y
are null use z; if x is null add x+y and divide by2; if none are null add
x+y+z and divide by 3;and that is just the first of several calculations.
I thought that as I have all the queries working now I could combine them in
a query that the input clerk could run each time a new set of records were
added and then they could retrieve the data without coming to me. But I could
not expect her to run about 30 separate queries in the right order each time
she finished her input.
 
Lyn,

It sounds like you could use a macro with a number of OpenQuery actions, one
foer each query. It will then just run all your queries sequentially.

Having said that, it certainly also sounds like you have a bit of a problem
with the design of your tables. The need to do this type of thing is
normally a sign of poorly normalised data.
 
Back
Top