Calculating Totals

Calculating Totals
It is often useful to ask questions about groups of data such as:
n What is the total number of properties for rent in each city?
n What is the average salary for staff?
n How many viewings has each property for rent had since the start of this year?
We can perform calculations on groups of records using totals queries (also called aggregate
queries). Microsoft Office Access provides various types of aggregate function including
Sum, Avg, Min, Max, and Count. To access these functions, we change the query type
to Totals, which results in the display of an additional row called Total in the QBE grid.
When a totals query is run, the resulting datasheet is a snapshot, a set of records that is not
updatable.
As with other queries, we may also want to specify criteria in a query that includes
totals. For example, suppose that we want to view the total number of properties for rent
in each city. This requires that the query first groups the properties according to the city
field using Group By and then performs the totals calculation using Count for each group.
The construction of the QBE grid to perform this calculation is shown in Figure 7.7(a)
and the resulting datasheet in Figure 7.7(b). The equivalent SQL statement is given in
Figure 7.7(c).
For some calculations it is necessary to create our own expressions. For example, suppose
that we want to calculate the yearly rent for each property in the PropertyForRent table
retrieving only the propertyNo, city, and type fields. The yearly rent is calculated as twelve
times the monthly rent for each property. We enter ‘Yearly Rent: [rent]*12’ into a new
field of the QBE grid, as shown in Figure 7.8(a). The ‘Yearly Rent:’ part of the expression
provides the name for the new field and ‘[rent]*12’ calculates a yearly rent value for each
property using the monthly values in the rent field. The resulting datasheet for this select
query is shown in Figure 7.8(b) and the equivalent SQL statement in Figure 7.8(c).

Figure 7.7 (a) QBE grid of totals query to calculate the number of properties for rent in each city;(b) resulting datasheet; (c) equivalent SQL statement.