Microsoft Office Tutorials and References
In Depth Information
Six Kinds of Queries
Summary Query Functions
The total of all values in the field
The average of all values
The lowest value
The highest value
The number of values
The standard deviation of the values
The variance of the values
The first value
The last value
The Group By, Expression, and Where choices in the Totals drop-down list
are for including fields you’re not performing a function on:
✦ Group By: For choosing which fields to show totals for.
✦ Expression: For creating a calculated field.
✦ Where: For setting criteria (you can’t include the field in the query).
A calculation query is one in which calculations are performed as part of the
query. For example, you can calculate the sales tax on items sold or total the
numbers in two fields in the same record. The beauty of a calculation query
is that the data is recomputed each time you run the query. If the data used
to make a calculation changes, so does the result of the calculation. If you
were to include the calculation in a database table, you would have to
recalculate the data yourself each time one of the values changed. With a
calculation query, Access does the math for you.
To construct a calculation query, you create a new field in the Query grid for
storing the results of the calculation; then enter a name for the field and a
formula for the calculation. Follow these steps to create a calculation query:
1. Create a query as you normally would and be sure to include the
fields you want to use for calculation purposes in the Query grid.
2. In the Field box of a blank field, enter a name for the Calculation field
and follow it with a colon.
In Figure 4-8, I entered Subtotal: . The purpose of the new Subtotal field is
to calculate the Unit Price × the Quantity.