Query Effeciency Question

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?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
Good. Access db's need a compact and repair from time to time too, if you hadn't already been doing that.
ξ
 

Forum statistics

Threads
1,081,773
Messages
5,361,207
Members
400,617
Latest member
barron1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top