Try the Access Course for Free!

Aggregate Function Queries in Access – Tutorial and Instructions

/ / Access 2013, Access 2016, Latest, Microsoft, Office 2013, Office 2016, Office 365
Aggregate Function Queries in Access - Tutorial: A picture of a user selecting an aggregate function to perform within a query in Access 2016.

Aggregate Function Queries in Access: Overview

            You can create aggregate function queries in Access that perform a mathematical function on another grouped field in a query. Aggregate function queries in Access are usually shorter queries often used for summary totals in reporting.

            For example, if you want to know the sum of sales for each salesperson in your company, you can show this in a summary query. However, this assumes 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.

            To create aggregate function queries in Access, open the query in design view. Then click the “Design” tab in the “Query Tools” contextual tab within the Ribbon. Then click the “Totals” button in the “Show/Hide” button group. This will add an additional row into your query called the “Total:” row. Under each field in the query within this row, you will see the words “Group By.”

            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” fields you would leave the “Group by” value intact. Doing this will then group all records where the “Salesperson” value is the same.

Aggregate Function Queries in Access - Tutorial: A picture of a user selecting an aggregate function to perform within a query in Access 2016.

Aggregate Function Queries in Access – Tutorial: A picture of a user selecting an aggregate function to perform within a query in Access 2016.

            Next, under the “SalesAmount” field, click into the “Total:” row and select the function to perform on this field. The function you choose is performed for each unique grouping of the other field (or fields) by which you grouped the records. So, in this example, you select the “Sum” function under the “SalesAmount” field. This query then shows you the “Sum” of the “SalesAmount” field for each unique group of values in the “Salesperson” field. These functions are called “aggregate functions,” as they perform a function upon the aggregation of values in a field.

Aggregate Function Queries in Access: Instructions

  1. To create aggregate function queries in Access, open the query in query design view.
  2. Then click the “Design” tab in the “Query Tools” contextual tab within the Ribbon.
  3. Then click the “Totals” button in the “Show/Hide” button group.
  4. A new “Totals:” row appears in the QBE grid.
  5. Click into the “Totals:” row under the fields by which you want to group the records.
  6. Then select “Group By” from the drop-down menu.
  7. Click into the “Totals:” row under the field(s) upon whose values you wish to perform a function for each unique grouping created by the other field(s).
  8. Then select the mathematical function you want to perform from the drop-down menu.
  9. Click “Save” in the Quick Access toolbar to save your changes.

Aggregate Function Queries in Access: Video Lesson

            The following video lesson, titled “Function Queries,” shows you how to create aggregate function queries in Access 2016. This video lesson is from our complete Access training, titled “Mastering Access Made Easy v.2016-2013.”

TOP