Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 36 ยท Queries

Totals Queries, Grouping and Calculated Fields in Access

Summarize data in Access with the Totals row: Group By, Sum, Count, Avg, Min, Max, Where and Expression, filtering groups, grouping on expressions and avoiding double-counting.

Detail rows are rarely the final answer. A manager wants total sales per customer, the average price per category or the number of orders per month. A totals query (an aggregate query) groups rows that share a value and calculates a summary for each group.

In Access you switch it on with a single button: the Totals row. This lesson explains each Totals option, the difference between filtering rows and filtering groups, calculated fields inside totals queries and the mistakes that produce wrong numbers.

Turning on the Totals row

In Query Design view, click Totals (the sigma icon, ฮฃ) on the Query Design tab. A new Total row appears in the grid with Group By under every field. Change that setting per column.

Total option Meaning
Group By Rows with the same value form one group
Sum Adds the values (numbers only)
Avg Average, ignoring Null
Min / Max Smallest or largest value (works on text and dates too)
Count Counts rows where the field is not Null
StDev / Var Standard deviation / variance
First / Last First or last value found, with no guaranteed order
Expression The field holds a calculation that already uses aggregate functions
Where Filters rows before grouping; the field is not shown

How a totals query runs

  1. Where
Filter the raw rows
  1. Group By
Put matching rows in groups
  1. Aggregate
Sum, count, average each group
  1. Criteria on totals
Filter the groups (Having)

Filtering before grouping is faster than filtering after.

1 / 2

A first totals query

To count orders and sum sales for each customer: add Customers, Orders, OrderDetails and Products; put CustomerName (Group By), OrderID (Count) and a calculated field Sales: [Quantity]*[UnitPrice] (Sum) in the grid. In SQL:

SQL
SELECT Customers.CustomerName,
       Count(Orders.OrderID) AS OrderCount,
       Sum(OrderDetails.Quantity * Products.UnitPrice) AS Sales
FROM ... 
GROUP BY Customers.CustomerName;

Here is a runnable version:

SQL Orders and sales per customer

Press Run (or Ctrl + Enter) to see the result here.

Note COUNT(DISTINCT ...). Access SQL does not support COUNT(DISTINCT x) directly. The Access workaround is a two-step query: one query selects the distinct values, and a second counts them. Keep that in mind when moving examples between engines.

Where versus criteria on a total

You can filter in two places, and they do different things.

  1. Where (or a plain criterion on a Group By field): removes rows before they are grouped. "Only orders from March."
  2. A criterion on an aggregated field: removes groups after they are summed. "Only customers whose total sales exceed 100." In SQL this is HAVING.

In the grid, put Where in the Total row of a field you do not need to show and write its criterion in the Criteria row. To filter groups, write the criterion (>100) under the field whose Total is Sum.

SQL WHERE filters rows, HAVING filters groups

Press Run (or Ctrl + Enter) to see the result here.

Putting row filters in WHERE instead of HAVING is faster, because fewer rows get grouped.

Count: what exactly does it count?

  • Count([OrderID]) counts rows where OrderID is not Null.
  • Count(*) counts all rows, including those with Nulls. In the Totals row, choose Count on the primary key field to get the same effect, or write Count(*) in SQL view.
SQL Count(*) versus Count(field)

Press Run (or Ctrl + Enter) to see the result here.

Avg, Null and weighting

Avg ignores Null values. If three students have scores 80, Null and 100, the average is 90, not 60. If a Null should count as zero, convert it first with Nz([Score],0). Averages of averages are also misleading; average the raw rows instead.

Grouping on expressions

You can group by a calculated field. Group sales by month by typing SaleMonth: Format([OrderDate],"yyyy-mm") in the Field row, with Total set to Group By. In SQL:

SQL
SELECT Format(OrderDate, "yyyy-mm") AS SaleMonth, Sum(Amount) AS Sales
FROM Orders GROUP BY Format(OrderDate, "yyyy-mm");
SQL Sales per month

Press Run (or Ctrl + Enter) to see the result here.

Calculated fields on totals

Mixing aggregates and plain calculations gets tricky. Two patterns:

  • Aggregate of a calculation: Sum([Quantity]*[UnitPrice]). In the grid, type Sales: [Quantity]*[UnitPrice] and set Total to Sum.
  • Calculation on aggregates: Avg: Sum([Amount])/Count(*). Set Total to Expression.

Write the expression using whole aggregate functions when you choose Expression, or break the problem in two queries: one that totals, and a second based on the first that does the arithmetic.

IIf inside Sum: conditional totals

A powerful trick is a conditional sum. To total only furniture sales while still grouping by month, use IIf inside Sum:

Text
FurnitureSales: Sum(IIf([Category]="Furniture",[Amount],0))

Standard SQL uses CASE:

SQL Conditional totals

Press Run (or Ctrl + Enter) to see the result here.

Mistakes that give wrong totals

  • Extra fields in the grid. Every non-aggregated field is a grouping field. Adding a ProductName column splits each customer into one row per product.
  • Summing a parent field across a join. A CreditLimit repeated for each order is multiplied. Sum child fields, or total each table separately.
  • Joining to a table with duplicate keys inflates counts.
  • Expecting First/Last to mean earliest/latest. They return arbitrary rows. Use Min and Max with dates.
  • Forgetting Nulls. Sum of nothing is Null, not zero; wrap with Nz.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between Where and a criterion on a Sum field?

    Where removes raw rows before they are grouped, which is faster. A criterion on an aggregate removes whole groups (HAVING).
  2. 2. Three scores are 80, Null and 100. What does Avg return?

    Avg ignores Null, so it computes (80+100)/2 = 90.
  3. 3. Which pattern adds up only furniture sales in an Access query?

    IIf inside Sum returns the amount for furniture and zero otherwise.
  4. 4. You add ProductName to a customer totals query and the rows multiply. Why?

    Every Group By field creates its own group; remove fields you do not want to group by.

Summary

  • Click Totals (ฮฃ) to add the Total row, then choose Group By, Sum, Count, Avg, Min, Max, Expression or Where per field.
  • Use Where to filter rows first and a criterion on an aggregate to filter groups.
  • Count(*) counts all rows; Count(field) skips Nulls; Avg ignores Null.
  • Group on expressions like months, and use IIf inside Sum for conditional totals.
  • Extra grouping fields and joined parent values are the usual causes of wrong totals.

Next: Parameter, Crosstab and Action Queries