# 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:

- Predefined calculations, called "totals," to compute the following amounts for groups of records or for all the records combined in the query: sum, average, count, minimum, maximum, standard deviation, or variance.
- A custom calculation to perform numeric, date, and text calculations on each record using data from one or more fields. You need to create a new calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.) directly in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) for these types of calculations.

** Tip ** 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.

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 (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.

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

Select |
To find the |
Use with thesefield 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 |

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 (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. |

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 (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.

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 (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.

Handling 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`