Data Management

Build Your Skills: Avoid problems with aggregate functions in Access reports

Aggregate reports in Microsoft Access

In database terms, an aggregate refers to a group or subset of records. A group can be as large as all the records in the data source or as small as only one—depending on how you group the data.

Microsoft Access offers two types of functions for summarizing groups of records: the native domain functions or SQL’s aggregate functions. In this article, I’ll compare domain and aggregate functions and show you how to avoid a few common problems when using SQL aggregates.

Knowing the difference is critical because aggregates don’t always return an error—they’ll return erroneous data and you might not even realize it. Learning this after your best client's customers start calling to complain that their invoices are showing the wrong amount due will be a little too late. Your credibility with your client will be suspect, even if you can quickly correct the problem.

Comparing domain and aggregate functions
In Access, you might see the term domain used interchangeably with aggregate; aggregate and domain functions aren’t interchangeable, even though they can return the same result. (Access .adp files don’t support domain functions.) A domain is simply a set of records, and domain functions evaluate a domain.

Domain functions are native to Access and are extremely flexible:
  • Domain functions limit the values evaluated.
  • Visual Basic for Applications (VBA) directly supports domain functions.

On the other hand, aggregate functions aren’t native to Access. They’re the SQL equivalent and they’re somewhat limited because:
  • SQL aggregate functions evaluate the entire group.
  • VBA requires that a SQL aggregate function be executed via a valid SQL statement.

Table A defines Access domain and SQL aggregate functions.
Table A
Domain and aggregate function comparison
SQL aggregate Access domain Use
Avg(field) DAvg(field,domain,criteria) Returns the mean or average of the values in field
Count(field) DCount(field,domain,criteria) Counts non-Null entries in field
Count(*) DCount(*) Counts the total number of rows
Sum(field) DSum(field,domain,criteria) Totals the values in field
Min(field) DMin(field,domain,criteria) Returns the smallest value in field
Max(field) DMax(field,domain,criteria) Returns the largest value in field
First(field) NA Returns the value in the first row
Last(field) NA Returns the value in the last row
StDev(field) DStDev(field,domain,criteria) Returns sample standard deviation
for field

StDevP(field) DStDevP(field,domain,criteria) Returns population standard deviation for field
Var(field) DVar(field,domain,criteria) Returns sample variance for field
VarP(field) DVarP(field,domain,criteria) Returns population deviation for field

Which one do you use?
Since domain and aggregate functions both perform the same task, you might be wondering when to use each. Most often, Access will tell you they aren’t interchangeable, and Access will “complain” if you use one inappropriately. Neither really has an advantage over the other. Here are a few guidelines you can use to determine which function is more appropriate:
  • Use both domain and aggregate functions in calculated controls and queries. Keep in mind that domain functions will slow down performance, so use them only when you need to limit the set of records you’re evaluating using the criteria argument.
  • Use domain aggregates to define a variable using VBA.
  • Use a valid SQL statement to execute a SQL aggregate via VBA.

Problem one: Knowing where to put the aggregate
When used in queries and forms, a SQL aggregate evaluates all the values within a group. Reports respond to aggregate functions in the same way, evaluating only the values in the report’s section (or group). Consequently, where you put the aggregate is important:
  • Aggregates in the detail section evaluate the group, but the report displays that result for each record.
  • Position an aggregate function in the report’s header or footer to summarize all the values in the report’s data source.
  • Position an aggregate function in a group’s header or footer to summarize the values in each group.
  • Don’t put aggregates in the page’s header or footer because they'll return an error.

Aggregates in the detail section
We can easily explore positioning aggregates in reports in Northwind, the sample database that comes with Access. Use the Report Wizard to create a simple report that includes the LastName, FirstName, and ReportsTo fields from the Employees table. Then, open the report in Design view and add an unbound text box to the detail section, just under the ReportsTo control. Enter this Control Source setting:
=Count(ReportsTo)

The results, which are fairly useless, are shown in Figure A. You’ll find little use for aggregates in the detail section. Notice that the aggregate returns the value 8 when we know there are actually nine records. I’ll explain this discrepancy later. For now, we’re just concerned with how the aggregate responds to each section of the report.

Figure A
An aggregate function in the detail section evaluates all the values in the group and returns the same value for each record in the group.


Aggregates in the report footer
In the report’s footer, an aggregate will evaluate the entire recordset. Following our example, the same aggregate will return the same result in the report’s footer, but it will return that value only once.

To make this modification, return the report to Design view and extend the report’s footer section. Then drag the unbound control from the detail section to the report’s footer and view the modified report in Print Preview. This time, the report, shown in Figure B, makes more sense because the aggregate reports the total number of employees, but it does so only once.

Figure B
Move the aggregate to the report section.


If the function returns #Error, you probably moved the function to the page’s footer section by accident. Check the function’s placement in Design view and move it.

Aggregates in the group header or footer
Now let’s suppose you want to group and count employees by their supervisor. The solution is simple enough—just group the report on the ReportsTo field and then add an aggregate to the group’s footer (or header, as the case may be). To do so, return to Design view, right-click the detail section’s title bar, and choose Sorting And Grouping from the resulting dialog box to display the Sorting And Grouping dialog box.

Choose ReportsTo in the Field/Expression column and open the group’s footer by choosing Yes from the Group Footer property’s drop-down list, as shown in Figure C. Then, close the dialog box.

Figure C
Group the records by the ReportsTo field.


Don’t move the unbound control this time. Instead, make a copy of it so you can see how the same function returns different results at the same time. First, select the unbound control that contains the aggregate function and press [Ctrl]C to copy it to the Clipboard. Next, select the report’s footer title and press [Ctrl]V to paste a copy of the unbound control into the report’s footer section. (You’ll probably want to reposition the copied control.) In Print Preview, compare the results of the two controls, as shown in Figure D. The same aggregate counts the number of employees that report to each supervisor and the total number of employees.

Figure D
The same aggregate function returns different results, depending on its position in the report.


Problem two: Knowing which field to evaluate
Earlier, I drew your attention to the fact that the aggregate function returns the value 8, even though there are nine records in the report. Grouping the report offers a bit of a clue. Aggregate functions, for the most part, ignore Null values. Consequently, the aggregate ignores Andrew Fuller because his ReportsTo value is Null.

Knowing which field to evaluate is as important as knowing where to position the aggregate. If you want to count actual employees, you must evaluate a field that contains an entry for each employee. In this case, the best candidate is the LastName field. To make this change, return to Design view, select the unbound control in the report’s footer, and enter
=Count(LastName)

As you can see in Figure E, the aggregate now counts all the records.

Figure E
Evaluating the LastName field counts all of the employees.


Refer to Table B to see which aggregates ignore Null values.
Table B
Domain and aggregate function comparison
SQL aggregate Considers Null
Avg(field) No
Count(field) No
Count(*) Yes
Sum(field) No
Min(field) No
Max(field) No
First(field) Yes
Last(field) Yes
StDev(field) No
StDevP(field) No
Var(field) No
VarP(field) No

Problem three: SQL aggregates must reference a field
The SQL aggregate functions have only one argument, and it must be a field reference. That means you can’t reference a control by its name, which can be puzzling if you aren’t aware of the argument’s requirement. For example, a grouped report on the Northwind Order Details table might exhibit the problem shown in Figure F. Notice that there’s no order total to the right of the Order Total label.

Figure F
An incorrect reference in an aggregate is a problem in a report.


To create the above problem, I added an unbound text box to the detail section, named it txtItemTotal, and then entered the following expression to return the item totals you see in Figure F:
([UnitPrice]*[Quantity])-([UnitPrice]*[Quantity])*[Discount]

Then, you enter a second text box control to the report’s footer section and enter this expression:
=Sum(txtItemTotal)

You might expect this expression to return an order total for each order (or group), but it doesn’t. The problem occurs when you reference txtItemTotal by name. When you open the report in Print Preview, Access displays a parameter prompt because Access can’t resolve the reference to txtItemTotal. If you click OK to clear the prompt, the aggregate displays nothing (see Figure F).

It’s easy to solve this particular problem. First, base the report on a query that evaluates the above item total expression (the expression in txtItemTotal). The new bound control will replace txtItemTotal. In the report’s footer, reference the query field using the following expression:
=Sum([Total])

The results, shown in Figure G, return the expected totals.

Figure G
A simple change in referencing returns the correct order totals.


SQL aggregate functions can be perplexing because they evaluate groups. I've shown you how to avoid a few common report problems when using aggregate functions. Proper placement and referencing is critical to accurate reporting.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments

Editor's Picks