Microsoft Access 2010 Bible

Chapter 18: Advanced Access Query Techniques

665

Suppose that you want to sum sales for each product category. I’ve shown you similar queries ear-

lier in this chapter, but this time I’ll perform the aggregation using an expression rather than using

an option in the Total row:

1. Start a new query and add tblProducts and tblSalesLineItems.


2. Click the Totals button (the Σ) on the ribbon and add Category from tblProducts to
   the QBE grid. Leave its total option set at Group By.


3. In the empty column to the right of Category, add the following expression to the Field row:

Sales: Sum(tblSalesLineItems.Quantity * tblSalesLineItems.

RetailPrice)

4. Make sure Access has set the Total row for this column to Expression.

Your query should be similar to Figure 18.25. Notice that the query uses two fields from
tblSalesLineItems to create the Sales calculated field.

FIGURE 18.25

A query using an Expression Total

Clicking on the Datasheet button on the ribbon displays the datasheet shown in Figure 18.26. The

Sales field is calculated by quantity and retail price from tblSalesLineItems, grouped by

product category.

qryAggregateExpression is included in the Chapter18.accdb database.