Using Temp Tables as Report Record Sources
Did you ever have an Access report with such a complicated underlying record source that you couldn't quite get it to work? Or maybe there were so many subqueries that you could measure the time it took to run in minutes rather than seconds? Or one of the calculated values wasn't right but you got lost trying to follow its dependencies to figure out where it went wrong?
Sometimes the simplest solution is to populate a temporary table whose only purpose is to serve as the backend for the report.
When Does it Make Sense?
There are a few situations when temporary tables make a lot of sense as report record sources. The following list is not intended to be exhaustive.
Reports with only a Few Detail Sections
Set-based operations achieve their efficiency when working on large data sets. If your report is only showing results for a handful of records, the efficiency advantage shrinks substantially. That's when other considerations–such as readability, ease of debugging, and algorithmic flexibility–become more important.
Reports with Complex Calculations
I'm not talking about simple SUMs, MINs, MAXes, and AVERAGEs. If those are your most complicated calculations, use a query for your recordsource.
What I'm talking about here are the kinds of calculations where you need to use the Zoom box on the query editor to see the whole thing. If you have half a dozen or more fields with 100-character formulas, that sucker will be a beast to troubleshoot. Keep in mind, you can't even put comments in your Access queries, so good luck figuring out what you were thinking six months ago when your users report a problem. It will probably also be dog slow.
Query optimizers in RDBMS'es are very good, but they're not mind readers. If your calculations are complex enough, you can often beat the performance of the optimizer by hand-crafting a custom function in VBA.
Reports with Subqueries of Subqueries (of Subqueries...)
If you find yourself going two or more levels deep with subqueries, that's a good indication that you may need to consider a different approach.
Sometimes you can replace those subqueries with Append or Update queries that add or update records in one or more temporary tables. Nested subqueries can really kill performance, especially if you don't know what you're doing. And even if you do know what you're doing, you can easily end up in a situation where you can't get your subqueries to produce an efficient query plan. There's no good way out of that situation.
Reports that will be Run Repeatedly
Let's say you have a report query that takes two minutes to run.
The user opens the report, looks at it for a few seconds, and then closes it to tweak their filtering criteria. Most of the underlying data could be the same, but it will still take two minutes to reload the report.
In those types of situations, you may be better off populating the underlying data once and then letting the user apply the criteria to the pre-populated data. The first run of the report might still take two minutes, but subsequent reloads will happen in seconds.
Reports where Accuracy is More Important than Speed
Sometimes a query is faster than using a temporary table, but there still may be a reason to go with the temp table approach.
It can be very difficult to see how a query calculates values for an individual record in a large dataset. And if that dataset may contain problematic edge and corner cases, those will be tough to isolate and test.
By using VBA functions and temporary tables to generate your data, it's easier to step through the process and verify that everything is working the way you expect.
When it's Vital that What You See is What You Print
I (almost) always show print previews of my Access reports rather than sending them straight to the printer. There is a small danger in this, though.
You see, when you preview a report on the screen, any underlying queries are executed and the results are used to populate the previewed report.
When you print a report that is previewed on the screen, any underlying queries are executed and the results are used to populate the printed report.
In other words, if the underlying data changes between the time it gets previewed on screen and the time the user hits the print button, then the printed output might not match what they saw on the screen!
Oftentimes this doesn't matter: the user just wants the latest data. But sometimes it matters a lot. If it's important that the data on-screen matches what's on the report, then it's important you populate a temporary table so that the previewed report and printed report remain identical.
When Does it NOT Make Sense?
Most of the time.
That's right. After listing all the situations where temporary tables are a good choice, I'm here to tell you that more than 95% of my Access reports do not use temporary tables.
However, they are a very important tool to have in your toolkit.
Pros and Cons
Advantages
- Easier to debug for complicated reports
- Report opens really fast once data is populated
- Reopening a report is near instant
- Provides a great deal of flexibility
- Can handle any situation
Disadvantages
- Additional work required to set up and maintain the temporary tables
- Will cause front-end bloat if temp table is local
- Populating data could take longer if done RBAR vs. using set-based operations
- Additional database objects clutter the navigation pane
- Need to refresh temp table contents occasionally so user doesn't see stale data
Image by Manfred Antranias Zimmer from Pixabay