Totals Queries in Access: GROUP BY, COUNT, SUM, and More

A totals query (also called an aggregate query) summarizes your data — instead of showing every individual record, it groups records together and calculates summary values like counts, sums, and averages. Totals queries are the foundation of most reporting and analysis work in Access.

Enabling Totals in the Query Designer

To create a totals query in the Query Designer:

1. Create a new query and add your table(s)
2. Add the fields you want to group by and aggregate
3. Click Totals on the Design tab (the Σ button), or go to View → Totals
4. A new Total row appears in the design grid

Each field in the grid now has a Total setting. The options are:

• Group By — groups records by this field's values
• Sum — adds up all values in the group
• Avg — calculates the average
• Min — finds the minimum value
• Max — finds the maximum value
• Count — counts the number of records
• StDev — standard deviation
• Var — variance
• First — first value in the group
• Last — last value in the group
• Expression — lets you write a custom expression
• Where — filters records before grouping (like a WHERE clause)

Basic GROUP BY Query

Example: Count orders and calculate total revenue by customer:

SELECT CustomerID, 
       Count(OrderID) AS OrderCount,
       Sum(OrderTotal) AS TotalRevenue,
       Avg(OrderTotal) AS AvgOrderValue
FROM Orders
GROUP BY CustomerID;

In the Query Designer:

• CustomerID: Total = Group By
• OrderID: Total = Count, rename to OrderCount
• OrderTotal: Total = Sum, rename to TotalRevenue
• OrderTotal (again): Total = Avg, rename to AvgOrderValue

Grouping by Multiple Fields

You can group by multiple fields — Access creates a group for each unique combination:

SELECT Year(OrderDate) AS OrderYear, 
       Month(OrderDate) AS OrderMonth,
       CustomerID,
       Sum(OrderTotal) AS MonthlyTotal
FROM Orders
GROUP BY Year(OrderDate), Month(OrderDate), CustomerID
ORDER BY Year(OrderDate), Month(OrderDate), CustomerID;

Filtering Before Grouping: WHERE

Use a WHERE clause (or the "Where" Total setting in the designer) to filter records before they are grouped:

SELECT CustomerID, Sum(OrderTotal) AS TotalRevenue
FROM Orders
WHERE OrderDate >= #1/1/2026#
GROUP BY CustomerID;

In the designer, add OrderDate to the grid, set Total to "Where", and enter the criteria >= #1/1/2026#. Uncheck the Show checkbox for this field — it is used for filtering only.

Filtering After Grouping: HAVING

HAVING filters the grouped results — it applies after the GROUP BY, unlike WHERE which applies before:

SELECT CustomerID, Sum(OrderTotal) AS TotalRevenue
FROM Orders
GROUP BY CustomerID
HAVING Sum(OrderTotal) > 10000;

This returns only customers whose total revenue exceeds $10,000.

WHERE vs. HAVING:

• WHERE filters individual records before grouping
• HAVING filters groups after aggregation
• You can use both in the same query

Counting Records vs. Counting Values

Count(*) counts all records in the group, including those with null values. Count([FieldName]) counts only records where the field is not null.

-- Count all orders
Count(OrderID)

-- Count orders that have been shipped (ShipDate is not null)
Count(ShipDate)

Joining Tables in Totals Queries

You can join tables before aggregating. This lets you show descriptive names instead of IDs:

SELECT c.CustomerName, 
       Count(o.OrderID) AS OrderCount,
       Sum(o.OrderTotal) AS TotalRevenue
FROM Customers AS c
LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName
ORDER BY TotalRevenue DESC;

The LEFT JOIN ensures customers with no orders still appear (with Count = 0 and Sum = null).

Calculated Fields in Totals Queries

You can include calculated fields in a totals query:

SELECT CustomerID,
       Sum(Quantity * UnitPrice) AS Revenue,
       Sum(Quantity * UnitPrice) - Sum(Quantity * CostPrice) AS Profit,
       (Sum(Quantity * UnitPrice) - Sum(Quantity * CostPrice)) / 
        Sum(Quantity * UnitPrice) AS MarginPct
FROM OrderDetails
GROUP BY CustomerID;

In the designer, set these calculated fields to "Expression" in the Total row.

Common Mistakes

"You tried to execute a query that does not include the specified expression as part of an aggregate function" — This error means you have a field in the SELECT clause that is neither in the GROUP BY nor wrapped in an aggregate function. Every field must be either grouped or aggregated.

Counting nulls — Count([FieldName]) skips nulls. If you want to count nulls, use Count(*) or Count(Nz([FieldName], 0)).

Grouping by a calculated field — In Access SQL, you must repeat the expression in the GROUP BY clause, not use the alias:

SELECT Year(OrderDate) AS OrderYear, Count(*) AS OrderCount
FROM Orders
GROUP BY Year(OrderDate);  -- Must repeat Year(OrderDate), not "OrderYear"

Conclusion

Totals queries are the workhorse of Access reporting. Once you understand the GROUP BY / aggregate function pattern and the difference between WHERE and HAVING, you can build virtually any summary report directly in SQL. Combine totals queries with crosstab queries and you have a complete toolkit for data analysis without ever leaving Access.