Multiple reports use same queries, how do I avoid rerunning querie

[Mark]

I'm working on several reports in an Access database and need some advice.

I have three reports that are all based on a series of queries, and only the
last one is specific to each report. It starts with a broad select query,
then queries those results to reformat some data, then queries those results
as a totals query and finally the report specific query runs to filter those
results down to only that necessary for that specific report. I've built a
macro to run all three reports, but each report calls all the underlying
queries to be run. Because of the redundant querying, it can take several
minutes to run all three reports.

Is there any way to run the general queries once, save the results and then
have each report run on those saved results? I think if I can eliminate the
need to rerun the same queries over and over again, I can cut down the
processing time.

Any help would be greatly appreciated. Thanks in advance!
Mark

 

[krissco]

I'm working on several reports in an Access database and need some advice.

I have three reports that are all based on a series of queries, and only the
last one is specific to each report. It starts with a broad select query,
then queries those results to reformat some data, then queries those results
as a totals query and finally the report specific query runs to filter those
results down to only that necessary for that specific report. I've built a
macro to run all three reports, but each report calls all the underlying
queries to be run. Because of the redundant querying, it can take several
minutes to run all three reports.

Is there any way to run the general queries once, save the results and then
have each report run on those saved results? I think if I can eliminate the
need to rerun the same queries over and over again, I can cut down the
processing time.

Any help would be greatly appreciated. Thanks in advance!
Mark

You can place the results of the penultimate query in a table - and
then query the table for each specific report.

1. Create a new query such as the following:
Select * into myTempTable
from mySharedQuery

2. Change the report's record source
select *
from myTempTable
where {something specific to the report}

3. In your macro, add a line "OpenQuery" for the query in step 1. Put
this new line prior to opening any reports.


This approach will likely work for you, however, I have some caveats:
1. Select into is not standard SQL - this technique won't help you in
a non-microsoft architecture.
2. Using this method creates/replaces a table in your database with
the name myTempTable. This leads to database bloat.
3. Select into is not multi-user safe (what happens if two different
users run the same macro at the same time?)
4. Depending on your data, fields in the new table may have the
incorrect data type.

So, SELECT INTO is an easy option and a quick fix, but not ideal under
many circumstances. It is fairly easy to come up w/ a solution that
addresses points 1, 3, and 4 (using DELETE and INSERT queries along w/
a user flag). Defeating bloat gets to be a lot of work (Tony Teows has
some code to start you down this path if you choose).

-Kris

 

[Mark]

Thanks Kris! I went with your idea - in part. I've created a table that the
penultimate query dumps data to. Then the reports each query that table. The
first step in my macro is to run a delete query on that new table to
eliminate the data from the last run. So far, this seems to be a good way to
reduce the bloating problem. I guess time will tell.

Anyway, thank you for your insight and help. I greatly appreciate it!!!