When you display the results of a calculation in a field, the results aren't actually stored in the underlying table. Instead, Microsoft Access reruns the calculation each time you run the query so that the results are always based on the most current data in the database. Therefore, you can't manually update the calculated results.
Predefined calculations that use aggregate functions
To display the results of a calculation in a field, you can use a predefined calculation that Access provides or custom calculations you define. Use the predefined calculations, called aggregate functions or "totals," if you want to compute the following amounts for all records or for groups of records: sum, average, count, minimum, maximum, standard deviation, or variance. You choose one totals calculation for each field you want to calculate.
Calculations on all records
Calculations on groups of records
You can calculate some types of totals using the Simple Query Wizard. Or, you can calculate all types of totals using the Total row in the query design grid, where you select the aggregate function for the calculation you want to perform on a field.
In the query design grid, you can also specify criteria to affect the calculations and produce different query results. By adding criteria, you can limit the:
-
Groups before performing calculations on those groups.
-
Results after calculations on the groups are performed.
-
Records before they are grouped and before calculations are performed.
About aggregate functions and other options in the Total row of the query design grid
The following options in the query design grid's Total row are aggregate functions:
| Select | To find the | Use with these field data types |
|---|---|---|
| Sum | Total of the values in a field. | Number, Date/Time, Currency, and AutoNumber |
| Avg | Average of the values in a field. | Number, Date/Time, Currency, and AutoNumber |
| Min | Lowest value in a field. | Text, Number, Date/Time, Currency, and AutoNumber |
| Max | Highest value in a field. | Text, Number, Date/Time, Currency, and AutoNumber |
| Count | Number of values in a field, not counting Null (blank) values. | Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, and OLE Object |
| StDev | Standard deviation of the values in a field. | Number, Date/Time, Currency, and AutoNumber |
| Var | Variance of the values in a field. | Number, Date/Time, Currency, and AutoNumber |
You use the First or Last functions to return the first or last record in the group you are performing calculations on. These functions return the first or last record as it was entered in chronological order. Sorting the records has no effect on these functions.
Group By, Expression, and Where options
The following table summarizes what each option does:
| Select | To |
|---|---|
| Group By | Define the groups you want to perform the calculations for. For example, to show total sales by category, select Group By for the CategoryName field. |
| Expression | Create a calculated field that includes an aggregate function in its expression. Usually, you create a calculated field when you want to use multiple functions in an expression. |
| Where | Specify criteria for a field you aren't using to define groupings. If you select this option for a field, Access will hide the field in the query results by clearing the Show check box. |
Custom calculations and calculated fields
With a custom calculation, you can perform numeric, date, and text calculations on each record using data from one or more fields. For example, with a custom calculation, you can multiply one field's values by a set amount, find the difference between two dates stored in separate fields, combine several values in a Text field, or create subqueries. Using the options in the Total row in the design grid, you can perform the calculation on groups of records and calculate a sum, average, count, or other type of total on the calculated field.
For custom calculations, you need to create a new calculated field directly in the design grid. You create a calculated field by entering an expression into an empty Field cell in the query design grid.
The expression can be made up of multiple calculations as the following example shows:
Sum([UnitsInStock]+[UnitsOnOrder])
You can also specify criteria for a calculated field to affect the results of the calculation.
You don't have to display the results of a calculation in a field, however. Instead, you can use them as criteria to determine the records the query selects or to determine which records to perform an action on. For example, you can specify the following expression in the Criteria row to tell the query to return only records that have values in the RequiredDate field that are between today's date and three months from today's date.
Updating data with calculations
You can also use a calculation to update data from an update query. For example, you can enter the following expression in the Update To cell to increase all the values in the UnitPrice field by 5 percent.
Handling Null values in calculations
The aggregate functions won't include records containing blank (Null) values in their calculations. For example, the Count function returns a count of all the records without Null values. If you want to find the total number of records including those with Null values, use Count with the asterisk (*) wildcard character.
Count(*)
To count Null values when using the other aggregate functions, use the Nz function, which converts Null values to zeroes so they are included in a calculation.
FreightPercentage: Sum(Nz([Freight], 0)) / Sum(Nz([Subtotal]), 0) * 100
When you use an arithmetic operator (+, -, *, /) in an expression and the value of one of the fields in the expression is Null, the result of the entire expression will be Null. If some records in one of the fields you used in the expression might have a Null value, you can convert the Null value to zero using the Nz function as shown in the following example:
Nz([UnitsInStock], 0) + Nz([UnitsOnOrder], 0