Share this Page URL

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

Sorting, Filtering, and Retrieving Data 217 6. This summary view includes a single entry for each campaign, with a total of the campaign's expenses. This query offers a way to see how much has been spent versus how much is budgeted. In the next section, we'll add a calculated field to the query to see total expense as a percentage of the budget. Click File, Save As, and save this version of the query as qryExpenseSummary. Summarizing data with the Simple Query Wizard If you're using the Simple Query Wizard to create a query, you can summarize data as long as a field with the Number data type is included in the query. After selecting the tables and fields for the query, select the Summary option in the wizard, and then select the summary function you want to use--Sum, Average, Count, and so on. Using an Expression to Create a Calculated Field In Chapter 2, the guidelines we listed for good table design included not storing calculated values. But what sort of data analysis could you do without making calculations? You can create a calculated field in a query (or a form or report) that derives its values from the expression you use. The calcu- lation occurs only when you run the query. The results of the calculation aren't stored in the data- base. What does Where mean? You can select Where from the list in the Total row to use that field for entering criteria but not show the field in the query's results. Choosing Where in a summary query has the effect of clearing the Show check box.