Microsoft Office Tutorials and References
In Depth Information
Working with Totals, Subtotals, Averages, and Such
Working with Totals, Subtotals, Averages, and Such
So far, all the calculations in the queries we’ve discussed operate on
individual fields within records. Suppose that you want a different sort of total,
such as the total dollar amount of all sales in all records. You can perform
such a calculation in two ways:
Report: The better — and perhaps easier — way is to use a report rather
than a query. Reports provide more flexibility and allow you to display
the information in more meaningful ways than queries do.
For the goods on creating reports with totals and subtotals, see Book V,
Chapter 1.
Totals query: The other approach is to use a totals query. A totals query
doesn’t give you the flexibility or pretty output that a report does, but
it’s useful when you want to perform some quick calculations on the fly
without formatting a fancy report.
If you just want to do some quick subtotals, totals, or other multirecord
calculations, and you really don’t care how the data looks onscreen or in print,
you can use a query to do the math. As for the other multirecord
calculations we just mentioned, Table 2-9 lists all the calculations you can do in a
totals query.
Table 2-9
Operations Available in a Totals Query
Choice
Returns
Avg
Average of records in field
Count
How many records
First
Value stored in first record
Nothing (used only for grouping)
Group by
Value stored in last record
Last
Highest value in all records
Max
Min
Lowest value in all records
StDev
Standard deviation
Sum
Sum of records in field
Var
Variance
To create a query that performs calculations on multiple records, start with
a normal select query that contains the table (or tables) on which you want
to perform calculations. Then do either of the following:
Search JabSto ::




Custom Search