If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple reports use same queries, how do I avoid rerunning querie
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 |
#2
|
|||
|
|||
Multiple reports use same queries, how do I avoid rerunning querie
On Jul 11, 12:50 pm, Mark wrote:
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 |
#3
|
|||
|
|||
Multiple reports use same queries, how do I avoid rerunning qu
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!!! "krissco" wrote: On Jul 11, 12:50 pm, Mark wrote: 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 |
Thread Tools | |
Display Modes | |
|
|