Invoice report prints wrong totals and takes 40 seconds
rptInvoices groups by customer and prints a statement: a customer group header, the detail rows, and a customer subtotal. The page footer contains a text box with =Sum([Amount]) and a 'Page x of y' label. Current problems: the Amount sum shows the same grand total on every page; customer names are cut off after about 40 characters once the department enlarged the font; the group header appears only on the first page of a customer group that spans three pages; preview takes about 40 seconds because the Amount control's source is a DLookup against tblRates evaluated per row; and the final customer's subtotal prints alone on a nearly blank last page. The department wants each customer to start on a new page, a grand total at the end, and the report to preview in a few seconds.
1. Explain why the page-footer sum is wrong and specify where each total belongs.
Read the worked answer
In an Access report, an aggregate in a page header or page footer is evaluated over the report's entire recordset, not over the current page. So =Sum([Amount]) in the page footer prints the grand total on page 1, page 2 and every page after - which is exactly the symptom. This is the opposite of the paginated-report tools where a page-footer aggregate is page-local. Access has no built-in per-page aggregate; faking one requires a hidden running-sum control and a difference calculation, and it is rarely worth the fragility.
Correct placement:
- Customer subtotal - a text box in the customer group footer,
=Sum([Amount]). It resets per group because the group footer is a new scope. - Grand total - the report footer,
=Sum([Amount]), or=Sum([txtAmount])referring to the detail control. - Page x of y - the page footer is right for this one:
="Page " & [Page] & " of " & [Pages]. - Set the group's Force New Page to Before Section so each customer starts a page.
If a true per-page subtotal is ever demanded, state the cost: a running-sum control with Running Sum = Over Group, plus a second control computing the delta, and it breaks whenever pagination changes.
2. Bring preview time down from 40 seconds and explain the cause of the delay.
Read the worked answer
A DLookup in a control source runs once per rendered row, and each call is a separate query against a table Access must locate, open and index-search. With a few thousand detail rows that is a few thousand round trips. That is the 40 seconds; it is not report rendering.
Fix:
- Join instead of lookup. Add tblRates to the report's record source in the query designer, join on the rate key, and bind the control to the joined column. One pass, one query plan.
- Save the query and set the report's Record Source to the saved query name rather than an embedded SQL string, so the plan can be reused and indexed.
- Kill expression-based criteria that prevent index use - functions wrapped around indexed columns,
Like '*x*'on a large table, or criteria that mix data types and force implicit conversion. - Check Sorting and Grouping. Grouping or sorting on an unindexed field or on a calculated expression forces Access to build and sort a temp result; add the index or group on the base column.
Also confirm the report is not opening in Print Preview while bound to a query with a parameter it re-prompts for, and set the report's record source to a snapshot-friendly query if the data does not need live updating.
3. Customer names are truncated and the last subtotal lands on a blank page. Address both.
Read the worked answer
Truncation. Widening the font made the text wider than the control; enlarging the font does not enlarge the control. Two separate properties are needed:
- Set Can Grow = Yes on the name text box so it expands vertically to fit wrapped text. This must be set in the Property Sheet, which is available in Layout view, but it only works when the control is not sharing a horizontal band with neighbours - Can Grow is ignored in a horizontal or overlapping layout, so remove the layout for that control first.
- Check Field Size in the underlying table. If the field is Short Text 40, no amount of formatting will show more; the data was truncated at entry.
Also confirm the name control is not a calculated expression with a fixed-width string function applied.
The blank last page is almost always the group footer or report footer being pushed past the page break. Check three things: Force New Page set on the group footer rather than before the group header (which creates an empty page after every group); the report footer's height plus the last group footer exceeding the remaining page space - shorten it, and set the group footer's Keep Together to No; and the page bottom margin plus the section height crossing the printable boundary. Preview against a full-size sheet before assuming a code problem.