About calculations in a query (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

There are many types of calculations you can perform in a query. For example, you can calculate the sum or average of the values in one field, multiply the values in two fields, or calculate the date three months from the current date. You perform calculations in a query using:

 Tip   Try Office 2010 In Access 2010, you can add calculated fields to tables as well as queries, helping to make your database more flexible. Read an article or try Office 2010.

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.

ShowPredefined 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 (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) 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 that show in a query

Callout 1 Calculations on all records
Callout 2 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.

ShowAbout aggregate functions and other options in the Total row of the query design grid

ShowAggregate functions

The following options in the query design grid's Total row are aggregate functions (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.):


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 (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) (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

ShowFirst and Last functions

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.

ShowGroup 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 (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) in its expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.). 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.

ShowCustom 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 (subquery: An SQL SELECT statement that is inside another select or action query.). 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 (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) into an empty Field cell in the query design grid.

Calculated field 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.

Calculation to define criteria for retrieving records

ShowUpdating data with calculations

You can also use a calculation to update data from an update query. For example, you can enter the following expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) in the Update To cell to increase all the values in the UnitPrice field by 5 percent.

Calculation to update data

ShowHandling Null values in calculations

The aggregate functions (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) won't include records containing blank (Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.)) 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 (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) (+, -, *, /) in an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) 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
            
 
 
Applies to:
Access 2003