Aggregate Function Queries in Access – Instructions

Aggregate Function Queries in Access: Video Lesson

            This video lesson, titled “Aggregate Function Queries,” shows you how to create aggregate function queries in Access 2019 and Access for Microsoft 365. This video lesson is from our complete Access tutorial, titled “Mastering Access Made Easy v.2019 and 365.”

Overview of Aggregate Function Queries in Access

            Aggregate function queries in Access let you create summary queries that perform a mathematical function, called an “aggregate function,” on a field, based on groups of values found in another grouped query field or grouped query fields. These are usually shorter queries, often used for reporting.

            For example, to see the sum of sales for each salesperson in your company, you can use an aggregate function in a summary query, if you are recording the salesperson for each sale. To do this, you must first create a query that has the “Salesperson” field, followed by the “SalesAmount” field. You then group by the values in the “Salesperson” field and sum the values of the “SalesAmount” field for the records in each unique grouping of values in the “Salesperson” field.

            To create a summary query using aggregate functions in Access, open the query in design view and add the fields needed for grouping, the fields to calculate for each grouping, and any fields needed for criteria purposes, in that order. Then click the “Totals” button in the “Show/Hide” button group on the “Design” tab of the “Query Tools” contextual tab in the Ribbon. Doing this adds a new “Total” row to the QBE grid of your query. Under each field in the query in this row, the words “Group By” appear, by default.

            The “Group By” value indicates that the query will group all records in that field that contain the exact same value. So, using our example, under the “Salesperson” field you would leave the “Group by” value intact, which will then group all records where the “Salesperson” value is the same.

A picture of a user creating an aggregate function query in Access.

            You then use the drop-down in the “Total” row of the field upon which to perform the aggregate function to select the name of the aggregate function to perform on this field for each unique grouping created by the other field, or fields, by which you grouped the records.

            So, continuing our previous example, under the “SalesAmount” field, click into the “Total” row and select the “Sum” function under the “SalesAmount” field. This query then shows the sum of the “SalesAmount” field for each set of records grouped by the values in the “Salesperson” field. These types of functions are called “aggregate functions” because they perform a function upon the aggregation of values in a field.

            The grouping and aggregate function choices available in the drop-down in the “Total” row in Access include “Group By,” for grouping fields, and a variety of standard, aggregate functions. There is also an “Expression” choice, which you can choose for query fields where you create a calculated field in a summary query and want it excluded from being used for grouping or aggregate functions. This choice lets you reference fields aggregated in the query.

            For example, if you have a “Sum” aggregate choice on a “SalesAmount” field in the query, you can refer to it in a calculated field as [SumOfSalesAmount] if the calculated field uses the “Expression” choice. For example, Commission: [SumOfSalesAmount]*.25.

            It also includes a “Where” choice, which you can choose for fields you must add to the QBE grid for criteria purposes but which you do not want to show in the query results or include as part of the grouping or aggregate function calculations. After creating a summary query in Access, make sure to click the “Save” button in the Quick Access toolbar to save your changes.

Instructions on How to Create an Aggregate Function Query in Access

1. To create a summary query using aggregate functions in Access, open the query in design view and add the fields needed for grouping, the fields to calculate for each grouping, and any fields needed for criteria purposes, in that order.
2. Then click the “Totals” button in the “Show/Hide” button group on the “Design” tab of the “Query Tools” contextual tab in the Ribbon.
3. Doing this adds a new “Total” row to the QBE grid of your query.
4. Under each field in the query in this row, the words “Group By” appear, by default.
5. The “Group By” value indicates that the query will group all records in that field that contain the exact same value.
6. Then use the drop-down in the “Total” row of the field upon which to perform the aggregate function to select the name of the aggregate function to perform on this field for each unique grouping created by the other field, or fields, by which you grouped the records.
7. The grouping and aggregate function choices available in the drop-down in the “Total” row in Access include “Group By,” for grouping fields, and a variety of standard, aggregate functions.
8. There is also an “Expression” choice, which you can choose for query fields where you create a calculated field in a summary query and want it excluded from being used for grouping or aggregate functions.
9. It also includes a “Where” choice, which you can choose for fields you must add to the QBE grid for criteria purposes but do not want to show in the query results or include as part of the grouping or aggregate function calculations.
10. To save the query after creating it, click the “Save” button in the Quick Access toolbar.