Access includes a group of powerful built-in commands known as aggregate(or total) functions. Using aggregate functions, you can easily calculate totals for groups of records returned by the query specifications. Aggregate functions can calculate the sum, minimum, maximum, average, count, variance, and standard deviation. In the query design window, you should include a field to group by as well as the field that contains the values needed for the calculation. The values of aggregate calculations are not stored in the tableinstead, Access recalculates the totals each time the query is run.
To use aggregate functions in queries:
Queries with aggregate calculations are often used to form the basis for strategic analysis or statistical reports. Try creating a query that includes a SUM, MIN, MAX and AVG calculation for the same field. A report based on this query not only shows the total, but more importantly, the range of values that add up to that total. |
|
If you don't include a group, Access will calculate the totals for all the records returned by the query. |
|
You can also display the totals rows by selecting Totals from the View menu. |