Microsoft Office Tutorials and References
In Depth Information
Working with Totals, Subtotals, Averages, and Such
line for each
Calculating subtotals in a query
To calculate subtotals, use another field in the query that identifies the field
on which the subtotals should be based. Set the Total row for that field to
a Group By value. In the top half of Figure 2-16, earlier in this chapter, we
added the ProductID field from the Order Details table to the QBE grid and
set its Total row to a Group By value.
The bottom half of Figure 2-17 shows the result of that query in Datasheet
view. The query returns the total extended price of orders for each
individual product. The figure shows sales of $100 worth of Golden Whistles, $2,000
worth of Kozmik Video Cameras, and so on.
The results of a totals query aren’t always easy to interpret. Alas, the small
amount of detail in the query results can make it difficult to see what the
calculated values are based on. In fact, the lack of detail in queries is the most
important reason why reports are so much better than queries for totals
and subtotals. In a report, you can include all the details you want — and
arrange things in such a way that you can easily grasp the meaning of every
calculated total just by looking at the report.
Filtering records based on calculated fields
You can filter records based on the results of a calculated field. Suppose
that you want to do a query like the one in Figure 2-16, but you want to see
only those records in which the total extended price is greater than or equal
to $1,000. In that case, just set the Criteria row for the calculated field
to >=1000. In Datasheet view, only those products with sales totals results
greater than or equal to $1,000 show up.