Microsoft Office Tutorials and References

In Depth Information

**Six Kinds of Queries**

Table 4-2

Summary Query Functions

Function

Returns

Sum

The total of all values in the field

Avg

The average of all values

Min

The lowest value

Max

The highest value

Count

The number of values

StDev

The standard deviation of the values

Var

The variance of the values

First

The first value

Last

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.

Book VII

Chapter 4

✦
Where:
For setting criteria (you can’t include the field in the query).

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