Evaluating Expressions with Calculated Fields

3 4

In addition to selecting a specific set of records, a query can return the results of expressions for each record. You already had a sneak peek at this capability in the section "Summarizing Data." You can add calculated fields to almost any query.

When working with a form, you can enter an expression in the ControlSource property of a control to create a calculated control. When you’re working with a query, you can enter an expression in a Field cell in the design grid. A calculated field, or calculated column, is any field that contains an expression.

The Order Details table offers a good example of using a calculated field. This table contains details for each item in each order. To calculate the extended price of each item, follow these steps:

1. Create a new query in Design view.
2. Add the Order Details table to the query.
3. Add the OrderID and ProductID fields to the design grid.
4. In the Field cell of the third column, enter the following expression (see Figure 9-11):

    ItemPrice: ([UnitPrice]*[Quantity])- ([UnitPrice]*[Quantity]*[Discount]) 

   

   Figure 9-11. Add a calculated ItemPrice field.

5. Run the query. Figure 9-12 shows the extended price for each item.

   

   Figure 9-12. A calculated field evaluates data from specific fields to return a value for each record.