Aggregate Functions, GROUP BY, and HAVING in Access SQL

Detailed order rows are useful for auditing, but managers usually need totals, counts, averages, and rankings. Aggregate functions in Access SQL convert transaction rows into compact summaries that can feed reports, dashboards, charts, and VBA procedures.

This lesson explains the difference between row filtering and group filtering, shows why every selected non-aggregate field belongs in GROUP BY, and demonstrates parameterized summary queries that remain readable as business rules grow.

The examples use sales data, yet the same pattern applies to inventory movements, attendance, support tickets, project hours, and any table where many rows must be summarized by customer, month, category, employee, or another dimension.

What this lesson adds to the Access SQL learning path

The lesson comes after SELECT, WHERE, JOIN, and data-editing topics because aggregation depends on a correct detail result. If the joined rows are duplicated or filtered incorrectly, the summary will faithfully total the wrong data.

Think of the query in two levels. The detail level decides which rows are eligible. The group level decides how those rows are collected and which groups survive. Keeping those levels separate prevents the common mistake of using HAVING for every condition.

Prerequisites and sample data model

The examples use Customers, Orders, OrderDetails, Products, and Categories. Customers has CustomerID as its primary key. Orders stores OrderID, CustomerID, OrderDate, and a status field. OrderDetails stores OrderID, ProductID, Quantity, and UnitPrice. Products stores ProductID, ProductName, and CategoryID. Primary and foreign keys should have compatible data types, and the join fields should be indexed when the tables become large.

Before running the examples, create a backup and confirm that table and field names match your database. If your names contain spaces, surround them with square brackets. Start with a SELECT query that returns a small result, then add grouping, parameters, or other advanced clauses one at a time.

Core syntax and mental model

Aggregate queries reduce many detail rows to one row per group. WHERE filters source rows before grouping, GROUP BY defines the group key, aggregate functions calculate values such as Sum, Count, Avg, Min, and Max, and HAVING filters the completed groups.

SELECT CustomerID,
       Count(*) AS OrderCount,
       Sum(TotalAmount) AS TotalSales,
       Avg(TotalAmount) AS AverageOrder
FROM Orders
WHERE OrderDate Between #2026-01-01# And #2026-12-31#
GROUP BY CustomerID
HAVING Sum(TotalAmount) >= 1000
ORDER BY Sum(TotalAmount) DESC;

Read the statement from the logical data flow rather than only from top to bottom: identify source rows, apply row-level filters, combine or group data where required, calculate the output, apply result-level filters, and finally sort the rows. This mental model helps explain why moving a condition between WHERE and another clause can change the result.

When to use this technique

• Monthly sales by customer or product category
• Counting open tickets by support agent
• Calculating average delivery time by carrier
• Finding groups whose totals exceed a management threshold
• Preparing compact datasets for charts and reports

A practical query with business rules

A useful report should exclude cancelled orders, accept a date range, group by customer, calculate order count and sales value, and return only customers whose sales exceed a chosen threshold.

PARAMETERS [pStartDate] DateTime,
           [pEndDate] DateTime,
           [pMinimumSales] Currency;
SELECT C.CustomerID,
       C.CustomerName,
       Count(O.OrderID) AS OrderCount,
       Sum(O.TotalAmount) AS TotalSales,
       Avg(O.TotalAmount) AS AverageOrderValue
FROM Customers AS C
INNER JOIN Orders AS O
    ON C.CustomerID = O.CustomerID
WHERE O.OrderDate Between [pStartDate] And [pEndDate]
  AND O.Status <> 'Cancelled'
GROUP BY C.CustomerID, C.CustomerName
HAVING Sum(O.TotalAmount) >= [pMinimumSales]
ORDER BY Sum(O.TotalAmount) DESC;

The GROUP BY list includes both CustomerID and CustomerName because both appear as detail fields in SELECT. The threshold belongs in HAVING because it depends on Sum. The date and status conditions belong in WHERE because they decide which order rows enter the groups.

Executing the query from VBA

For reusable automation, save the SQL as a named query and execute it through DAO. Explicit parameters are preferable to string concatenation because Access knows their types before it builds the execution plan. The following procedure demonstrates the pattern; adjust the QueryDef name and parameters to match the saved query used in this lesson.

Option Explicit

Public Sub ShowCustomerSalesSummary()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset

    On Error GoTo CleanFail

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryCustomerSalesSummary")
    qdf.Parameters("pStartDate") = DateSerial(2026, 1, 1)
    qdf.Parameters("pEndDate") = DateSerial(2026, 12, 31)
    qdf.Parameters("pMinimumSales") = 1000@

    Set rs = qdf.OpenRecordset(dbOpenSnapshot)
    Do While Not rs.EOF
        Debug.Print rs!CustomerName, rs!OrderCount, rs!TotalSales
        rs.MoveNext
    Loop

CleanExit:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

CleanFail:
    Debug.Print Err.Number, Err.Description
    Resume CleanExit
End Sub

The procedure opens a snapshot because the example reads results. Use an action-query execution method only for INSERT, UPDATE, DELETE, or make-table operations, and always inspect RecordsAffected. Close objects in a cleanup section so that an error does not leave a recordset open.

How to verify the result

1. Run the underlying source query and note the number of rows before applying the advanced clause.
2. Calculate one small case manually and compare it with the query output.
3. Test a parameter range that returns no rows and confirm that the calling code handles it.
4. Add a duplicate or Null value intentionally and observe whether the result matches the documented rule.
5. Compare the saved QueryDef result with the VBA Recordset row count.
6. Record the final field names and data types expected by the report or export routine.

Common errors and fixes

Performance, safety, and maintainability

A reliable aggregate functions in Access SQL workflow starts with a small, verified data set. Create a copy of the database, add a few rows whose values are easy to recognize, and run the query in read-only form before connecting it to a form, report, or VBA procedure. This habit separates SQL design mistakes from automation mistakes and makes every later test easier to explain.

Access SQL is close to standard SQL, but it has its own expression service, date delimiters, wildcard behavior, parameter handling, and query designer. When you work with aggregate functions in Access SQL, test the statement in the Access query window first. After the result is correct, move the same logic into a saved QueryDef or a VBA string. That sequence reduces quoting errors and exposes missing parameters early.

Use stable field names and explicit aliases. An alias should describe the meaning of a calculated column rather than repeat the expression. Clear aliases make reports easier to bind, simplify VBA recordset code, and prevent users from seeing names such as Expr1000. If a field name contains spaces or conflicts with a reserved word, wrap it in square brackets.

Null values require deliberate treatment. Aggregate functions, comparisons, joins, and expressions do not all handle Null in the same way. Decide whether Null means unknown, not applicable, or zero in the business rule. Use Nz only when replacing Null is semantically correct; otherwise you may hide missing data and produce totals that look precise but are not.

For production work, separate input validation from query execution. Validate dates, identifiers, and numeric limits before assigning them to parameters. Do not concatenate user text into SQL when a parameter is available. Parameterization improves type handling and reduces the risk of malformed statements, especially when apostrophes, locale-specific dates, or decimal separators are involved.

Performance should be measured with representative data. A query that is instant with twenty rows may become slow with hundreds of thousands. Index fields used repeatedly in joins and selective filters, avoid wrapping indexed fields in functions when a range comparison can be used, and return only the columns required by the next step. These choices matter more than cosmetic changes to SQL formatting.

A good test plan includes normal data, boundary values, Nulls, duplicates, and an empty result. Check whether the output still makes sense when no record matches, when one group contains a single row, and when two rows share the same sort value. Record the expected output before changing the query so that later refactoring can be compared against a known baseline.

Saved queries are useful documentation. Give each QueryDef a descriptive name, add a short comment in the surrounding VBA module, and keep the query focused on one responsibility. A saved query can be opened directly for troubleshooting, reused by forms and reports, and parameterized without building long SQL strings in code.

When a query changes data or feeds a critical report, log its inputs and the number of affected or returned rows. The log does not need to store sensitive values. A timestamp, query name, parameter summary, and row count are usually enough to diagnose unexpected behavior while preserving privacy.

Treat aggregate functions in Access SQL as part of a larger data pipeline. The query should have a clear input, a predictable output shape, and a documented consumer. Knowing whether the result will populate a chart, drive an update, export to Excel, or appear in a report determines the best aliases, ordering, Null policy, and level of detail.

Guided practice

Build the lesson in three passes. In pass one, return raw rows with only the required joins and filters. In pass two, introduce the main technique and verify the result with a small sample. In pass three, add parameters, aliases, sorting, and the VBA wrapper. Save each pass under a temporary name so you can compare the outputs and identify the exact change that caused a difference.

For a second exercise, change one business rule without rewriting the entire query. Examples include changing the reporting period, excluding cancelled orders, selecting a different category, or switching between detail and summary output. A well-structured query should allow this change in one filter, parameter, or calculated expression rather than in several disconnected places.

Finally, inspect the query from the perspective of another developer. Can they identify the input tables, parameters, output columns, and expected row granularity without opening every form that uses it? Rename unclear aliases, remove unused columns, and add a brief module comment describing the assumptions. This review often prevents more defects than another round of cosmetic formatting.

Related lessons in this series

This lesson follows the core SELECT, WHERE, JOIN, and data-editing topics. The related-link metadata also records the next planned lessons so the site can connect the series after publication. Review the existing guide on updating and deleting Access data with VBA when the workflow eventually changes records.

Conclusion

After completing this lesson, you should be able to design a totals query, choose the correct aggregate function, place conditions in WHERE or HAVING, declare parameters, and consume the result safely from VBA.

The most dependable approach is incremental: prove the SQL with controlled data, declare parameter types, verify Null and duplicate behavior, and only then automate the query. That workflow makes the result easier to trust and easier to maintain when tables, forms, or reporting requirements change.