Share this Page URL

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

Sorting, Filtering, and Retrieving Data 218 Calculated fields are often used to augment number or currency data. If you wanted to add 10 percent to each campaign budget, you'd use an expression such as [CampaignBudget]*1.10 . But calculated fields aren't restricted to numerical data. You can join the values in two text fields in a query, for example, by entering an expression such as the following in a blank column of the query design grid: Name: [FirstName] & " " & [LastName] Using the Expression Builder If you want to use the Expression Builder to create an expression in a query, right-click in the Criteria row, and then click Build. For more information about using the Expression Builder, see the section "Expressions and the Expression Builder," in Chapter 3. In the following steps, we'll modify the expense summary query to show the percentage of the overall budget we've spent so far. Create a calculated field 1. 2. 3. Open the query named qryExpenseSummary in Design view. Click File, Save As, and then name the query qryPercentOfBudget. In the first blank column, enter the following expression in the Field row: PercentOfBudget: Sum([AmountSpent])/[CampaignBudget] The first portion of this expression, PercentOfBudget: , creates a label for the calculated field. (You don't have to include a label such as this--if you don't, Access provides a label such as Expr1:--but using a label makes the field's purpose clearer.) The expression itself uses the