Series of action queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a procedure which runs when a particular summary form is opened.
The procedure runs a series of action queries which update data in many
tables so when reports are viewed, they are showing up to date data.
My question is, when Access encounters these queries (there's about 22 of
them) does it go through and execute them one-by-one or does it try do them
all at once? I ask because when opening this form it is extremely slow! It
takes about 20seconds to run this procedure.
Any help appreciated
Thanks, Ash.
 
It opens one, when that one's finished it opens the next.

I would question why you need to execute so many queries all of a
sudden. Ideally all that should happen is that one query (the one
that drives the report) would select the data from the tables and
display it in the report.

Cheers,
Jason Lepack
 
In theory yes I agree, however. What happens when a user views a report -
goes to a different part of the system and makes changes - goes back and the
main table has not been updated by the sub tables - therefore if the action
queries hadn't been run their change would not be reflected on the report.

Just a little more info - we have an accounts database which uses many sub
tables and then action queries to append these records to a main table (a
transactions table if you like) then the reports are generated from this.

Hope you get what I am saying.
 
ashg657 said:
In theory yes I agree, however. What happens when a user views a
report - goes to a different part of the system and makes changes -
goes back and the main table has not been updated by the sub tables -
therefore if the action queries hadn't been run their change would
not be reflected on the report.

Just a little more info - we have an accounts database which uses
many sub tables and then action queries to append these records to a
main table (a transactions table if you like) then the reports are
generated from this.

Hope you get what I am saying.
Click to expand...

Yes, you're saying that your table design is flawed. Changes in one table
should never require an update be issued to other tables. That suggests that
you are storing derived data rather than calculating it on the fly.
 
Ash,

After reading your last post I was just getting ready to post exactly
what Rick said.

Jason
 
Whilst I accept your opinions, maybe I should have mentioned earlier. This
project was given to me , i did not create it from scratch myself.
Unfortunately I do not have the time to rip it out and think about changing
the table design etc - I was after a simple fix , but if there is none,
because of the table design then that, i also, accept. Thanks for your
opinions, appreciated.

Jason Lepack said:
Ash,

After reading your last post I was just getting ready to post exactly
what Rick said.

Jason
Click to expand...
 
You're welcome! Here's hoping sometime down the road you can fix the
database and get your twenty seconds per form back ;)

Hope all goes well for you.

Cheers,
Jason Lepack
 
Whilst I have a bit of free time at work - could you possibly elaborate on
the point made earlier by yourself & Rick about you should never have a table
which requires updating from other tables.
Here's a brief description of what our database does:
Its an accounts system designed to have customers who pay bills, which are
generated from the system. Many different items can be put onto their bills
such as drinks, food, accomodation rates, subscription charges etc.
The database was designed to have a sub table called "Drinks" which hold the
drink had, date and time, which customer had it, quantity and total cost.
This data is then put into a two line entry using a query (1 credit entry, 1
debit entry) and put onto the main transactions table.
All of the reports are then produced using this main table. Hence why it
always needs updatng, and when the table gets large the slower the queries
run.
Given this scenario and what you both mentioned earlier, what SHOULD have
been done to prevent this problem?
Cheers.
 
What purpose does the main transactions table serve? Does it only hold data
derived by querying other tables? If so, it sounds like it could be
replicated by a single UNION query (qryDrinks + qryFood + etc) & your report
based on that.

I'm assuming there's a reason for separate subtables for Drinks, Food,
subscriptions, etc. but I suspect that's a big assumption (i.e., may not be
necessary). That can be a very ugly road to travel down...

In any case, if current data/app structure demands that foreign table data
*has* to be updated when a user makes changes, then that is when your update
should be run *for as narrow a dataset as possible (area, specific record,
etc.)*. Running updates for "everything" all at once (including updates to
Drinks when only Food needs updating?) will be slow and leaves your data in
an "incomplete" state for periods of time. However, running 5 second updates
in 6 different places (if user just updated a Drink, run Drink update, etc.)
won't be as bad and data would hopefully always be "report ready".

HTH,
 
Back
Top