Page 2 of 10PREVNEXT

Queries IV: Use functions and calculated fields in your queries

Creating a totals query

So, let's say you need to calculate how much you've spent on freight for the past month. If your data resides in a single field, you can do that job by using one of the built-in math functions in a totals query. Don't let the name fool you. A totals query can also average, count, and perform other math operations on your data.

Callout 1 Open your query in Design View, and on the Design tab, in the Show/Hide group, click Totals. The Total row appears in the query design grid.
Callout 2 Click the Total row in the field you want to calculate, and select the function you want to use. The one in the picture sums a column of numbers.

Those built-in functions are also called aggregate functions, because they calculate a single value from a group of values. You can also use the aggregate functions in Datasheet view, but if you do, someone can turn them off or hide them. That's less likely to happen if you use a totals query. Also, a totals query can group data, something you can't do with the aggregate functions in a datasheet.

Page 2 of 10PREVNEXT