ChuckRobert

Board Regular
Joined
Feb 26, 2009
Messages
64
I am running a group of queries in Access 2010 to sort through records in a table into a work center roster for a given facility. For now, I am using the first query to pull the records just for a given facility. Then, referencing that query, I pull another to get Section A Shift 1 from that facility, Section A Shift 2, and Section A Shift 3, and sometimes a crosstab query to count assigned rank for each shift. These form the foundation for sub-reports on the facility roster. I am trying to speed up processing of this report, as this is taking much longer than my previous version that did the same function, but had fewer possible sections. As it is more comprehensive, this report includes some sub-reports that will not have any data, and this seems to be slowing things down. I read yesterday about coding a cancel event on sub-reports with no data, and will try that, but wanted to understand if Access queries would run faster if I saved the first query I use to pull records just for a given facility back as a temp table, and had all subsequent queries for this report reference the temp table. When subsequent queries run off the first query, does Access re-run the first query going through all records every time, or is that first query effectively only ran once? If so, I would not expect any performance advantage of referencing subsequent queries from the initial query, or a temp table. Suggestions?
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
I don't know if Access would save the first query for re-use. I do however also employ temp tables to speed up performance by putting an initial set of data in the temp table and running subsequent queries/reports on the temp table. Given that this does improve speed a lot, I think it's likely the case that Access doesn't have the "intelligence" to save the data itself for future use based on a query plan - that's where bigger databases come into play with more sophisticated optimizers.
 

ChuckRobert

Board Regular
Joined
Feb 26, 2009
Messages
64
xenou,<o:p></o:p>
Thanks for the feedback! After posting my initial question I noticed that the report would take a long time to load, but would. However, it will not print as an error message pops up saying "Cannot open any more tables". I find this odd, as the entire report is based on only 1 table, but many queries/sub-reports, DLookups, and about 12 crosstab queries are used to display required data in the report. I wonder what Access considers a "table" to be. Clearly something else is going on here that I need to figure out! <o:p></o:p>
Before your reply I redirected all queries to use my initial query as the base, but that did not correct the condition. <o:p></o:p>
I will implement your suggestion, and hopefully get the report to load faster, but suspect the answer to my "Cannot open any more tables" may still remain. Thanks again!<o:p></o:p>
 

ChuckRobert

Board Regular
Joined
Feb 26, 2009
Messages
64
Just FYI. I transferred all objects into a new copy of Access and the performance is much better. I also found a few issues that were not being flagged in the older copy.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,824
Office Version
  1. 2019
Platform
  1. Windows
Good. Access db's need a compact and repair from time to time too, if you hadn't already been doing that.
ξ
 

Watch MrExcel Video

Forum statistics

Threads
1,127,658
Messages
5,626,138
Members
416,165
Latest member
hamburger138

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top