About calculations in a query (MDB)

Microsoft Office Access 2003

  • 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 directly in the design grid for these types of calculations.
  • 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 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:

    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

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

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

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