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.
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.
To display the results of a calculation in a field, you can use a predefined calculation that Microsoft Access provides or custom calculations you define. Use the predefined calculations, called "totals," if you want to compute the following amounts for groups of records or for all the records combined: 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 limit the groups for which totals are calculated, limit the records included in the calculation, or limit the results that are displayed after the calculation is performed.
A custom calculation enables you to 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 — for example, 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.
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.
