Share this Page URL

Chapter 6. Sorting, Filtering, and Retri... > Designing Select Queries - Pg. 216

Sorting, Filtering, and Retrieving Data 216 Summarizing and Grouping Records The records returned by the expense report query supply instructive details about marketing cam- paign data. But in the context of the overall campaign budget, expense detail would be more mean- ingful if it were summarized. In queries, you can use a set of built-in functions to display summary views of the data a query includes. The functions are mostly mathematical, but they also include Count, which you can use to determine the number of records in a recordset. In a summary view, you also group records on specific fields. In the following steps, we'll modify the expense report query to summarize and group records. Summarize and group records in a query 1. Open the query named qryExpenseReport in Design view. (If necessary, open the Hello- World6 database first, click Queries in the Database window, select qryExpenseReport, and then click Design.) Remove the criteria we entered in the previous section. Also delete the DatePurchased and Expense Type fields from the query. We're deleting these fields because doing so reduces the amount of detail in the query, which will let you see the effect of summarizing data more clearly. On the View menu, click Totals. A row labeled Total now appears in the design grid, as shown here: 2. 3.