Database Reference
In-Depth Information
To calculate the average balance, use the Avg function, as shown in Figure 2-19.
46
Criterion
(RepNum
must be 35)
Avg function
added to
Total row
FIGURE 2-19
Query to calculate an average
The query results appear in Figure 2-20.
Average balance
of all customers
of sales rep 35
FIGURE 2-20
Query results
GROUPING
You can also use functions in combination with grouping, where calculations affect groups of records. For
example, you might need to calculate the average balance for all customers of each sales rep. Grouping sim-
ply means creating groups of records that share some common characteristic. In grouping by RepNum, for
example, the customers of sales rep 20 would form one group, the customers of sales rep 35 would form a
second group, and the customers of sales rep 65 would form a third group. The calculations are then made
for each group. To group records in Access, select the Group By operator in the Total row for the field on
which to group.
EXAMPLE 10
What is the average balance for all customers of each sales rep?
In this example, include the RepNum and Balance fields in the design grid. To group the customer
records for each sales rep, select the Group By operator in the Total row for the RepNum column. To calcu-
late the average balance for each group of customers, select the Avg function in the Total row for the Balance
column, as shown in Figure 2-21.
