Ms Office Tune Up

To create a calculated field, you need to supply two details: a name for the field, and an expression that tells Access what calculation it must perform. Calculated fields are defined using this two-part form:

CalculatedFieldName: Expression

For example, here’s how you can define the PriceWithTax calculated field:

PriceWithTax: [Price] * 1.10

Essentially, this expression tells Access to take the value from the Price field, and then multiply it by 1.10 (which is equivalent to raising the price by 10 percent). Access repeats this calculation for each record in the query results. For this expression to work, the Price field must exist in the table. However, you don’t need to show the Price field separately in the query results.

You can also refer to the Price field using its full name, which is made up of the table name, followed by a period, followed by the field name, as shown here:

PriceWithTax: [Products].[Price] * 1.10

This syntax is sometimes necessary if your query involves more than one table (using a query join, as described in Section 6.3), and the same field appears in both tables. In this situation, you must use the full name to avoid ambiguity. (If you don’t, Access gives you an error message when you try to run the query.)

To add the PriceWithTax calculated field to a query, you need to use Design view. First, find the column where you want to insert your field. (Usually, you’ll just tack it onto the end in the first blank column, although you can drag the other fields around to make space.) Next, type the full definition for the field into the Field box (see Figure 6-22).

Figure 6-22. This query shows two fields straight from the database (ID and Name), and adds the calculated PriceWithTax field. The ordinary Price field, which Access uses to calculate PriceWithTax, isn’t shown at all.

Now you’re ready to run the query. When you do, the calculated information appears alongside your other columns (Figure 6-23).

Calculated fields do have one limitation since the information isn’t stored in your table, you can’t edit it. If you want to make a price change, you’ll need to edit the underlying Price field trying to change PriceWithTax would leave Access thoroughly confused.

Figure 6-23. The query results now show a PriceWithTax field, with the result of the 10 percent markup. The neat part’s that this calculated information’s now available instantaneously, even though it isn’t stored in the database. Try and beat that with a pocket calculator.

Before going any further, it’s worth reviewing the rules of calculated fields. Here are some pointers:

• Always choose a unique name. An expression like Price: [Price] * 1.10 creates a circular reference, because the name of the field you’re using is the same as the name of the field you’re trying to create. Access doesn’t allow this sleight of hand.

• Build expressions out of fields, numbers, and math operations. The most common calculated fields take one or more existing fields or hard-coded numbers and combine them using familiar math symbols like addition (+), subtraction (-), multiplication (*), or division (/).

• Expect to see square brackets. The expression PriceWithTax: [Price] * 1.10 is equivalent to PriceWithTax: Price * 1.10 (the only difference is the square brackets around the field name Price). Technically, you need the brackets only if your field name contains spaces or special characters. However, when you type in expressions that don’t use brackets in the query Design view, then Access automatically adds them, just to be on the safe side.

Popularity: 5% [?]