Placement is everything when using aggregate functions in Access reports

The same aggregate function will return different results in the same report. It's important that you understand how placement determines how an aggregate evaluates values in an Access report.

Aggregate functions evaluate the same value for many records to return a single value. That's a simplistic description (some will complain) but it's accurate. We use them a lot to display subtotals, grand totals, averages, and more, in reports. Knowing where to put the aggregates is important because report sections will return different results for the same aggregate! Knowing the following rules will help you avoid erroneous data (and embarrassment):

  • To evaluate grouped values, put the aggregate function in the group's header or footer.
  • To evaluate all values within a specific field, put the aggregate in the report's header or footer.

The following figure shows the SUM() function in a group and a report footer. It's the same function, =SUM([Unit Price]), but it yields different results. In the group footer, the SUM() function evaluates only those values in the current group. In the report's footer, the same function sums all of the unit price values in the report.

You can also repeat the result of an aggregate by placing it in the Detail section, but this isn't something you'll probably want to do - I've never found a use for it myself, but it's possible that you might. In addition, a report's page header or footer won't evaluate an aggregate at all - an aggregate in this section will return #Error.

As you can see, careful placement is critical when using aggregate functions in an Access report.