#MICROSOFT ACCESS QUERIES HOW TO#
Instructions on How to Create an Aggregate Function Query in Access
After creating a summary query in Access, make sure to click the “Save” button in the Quick Access toolbar to save your changes. 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. This choice lets you reference fields aggregated in the query.įor 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 if the calculated field uses the “Expression” choice. 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. 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. These types of functions are called “aggregate functions” because they perform a function upon the aggregation of values in a field. This query then shows the sum of the “SalesAmount” field for each set of records grouped by the values in the “Salesperson” field. So, continuing our previous example, under the “SalesAmount” field, click into the “Total” row and select the “Sum” function under the “SalesAmount” field. 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, 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.Ī picture of a user creating an aggregate function query in Access. The “Group By” value indicates that the query will group all records in that field that contain the exact same value.
Under each field in the query in this row, the words “Group By” appear, by default. Doing this adds a new “Total” row to the QBE grid of your query. Then click the “Totals” button in the “Show/Hide” button group on the “Design” tab of the “Query Tools” contextual tab in the Ribbon. 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. 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 do this, you must first create a query that has the “Salesperson” field, followed by the “SalesAmount” field. These are usually shorter queries, often used for reporting.įor 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. Overview of Aggregate Function Queries in AccessĪggregate 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.