Calculated Fields
When you started designing tables, you learned that it's a database crime to add information that's based on the data in another field or another table. An example of this mistake is creating a Products table that has both a Price and a PriceWithTax field. The fact that the PriceWithTax field is calculated based on the Price field is a problem. Storing both is a redundant waste of space. Even worse, if the tax rate changes, then you're left with a lot of records to update and the potential for inconsistent information (like a with-tax price that's lower than a no-tax price).
Even though you know not to create fields like PriceWithTax, sometimes you will want to see calculated information in Access. Before Boutique Fudge prints a product list for one of its least-loved retailers, it likes to apply a 10 percent price markup. To do this, it needs a way to adjust the price information before printing the data. If the retailer spots the lower price without the markup, they're sure to demand it.
Queries provide the perfect solution for these kinds of problems, because they include an all-purpose way to mathematically manipulate information. The trick's to add a calculated field: a field that's defined in your query, but doesn't actually exist in the table. Instead, Access calculates the value of a calculated field based on one or more other fields in your table. The values in the calculated field are never stored anywhereinstead, Access generates them each time you run the query.
Defining a Calculated Field
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 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.)
Note: Old-time Access users sometimes replace the period with an exclamation mark (as in [Products]![Price], which is equivalent.
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.
Now you're ready to run the query. When you do, the calculated information appears alongside your other columns. If you don't like the fact that your calculated information appears in a slightly messier formatwith more decimal places and no currency symbolyou can fix it up using the rounding and formatting features discussed later in this tutorial.
Calculated fields do have one limitationsince 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 fieldtrying to change PriceWithTax would leave Access thoroughly confused.
Note: An expression works on a single record at a time. If you want to combine the information in separate records to calculate totals and averages, then you need to use the grouping features.
Query Synchronization
Here's an interesting trick to try. Run the ProductsWithTax query and leave it open, displaying its results. Now, open the Products table that has the actual data, and then change the price of any product. Switch back to the ProductsWithTax query. Has the PriceWithTax value changed?
If you can't stand the suspense, fear notthe PriceWithTax is automatically refreshed to reflect the new price. Access automatically keeps query views synchronized with the live data in your table. When you change a record, Access noticesand it instantly refreshes the query window.
It's worth noting a few exceptions to this rule:
- Access doesn't notice if you insert a new record after you launch a queryto get that to appear in your query results, you need to refresh the results.
- If you change a record so it no longer appears in your query, it doesn't automatically disappear from view. If you have a query showing all products over $100, and you cut the price of one down to $50, then it still appears in your query result list (with the new price) until you refresh the results.
- Similarly, if you change a record that currently appears in your query so it no longer fits one of your filter criteria, it doesn't disappear from view until you rerun the query.
- If multiple people are editing the database on different computers, you don't see other people's changes right away.
To get the latest results, you can refresh individual records or the entire query. To refresh a single record, choose Home Records Refresh Refresh Record. To rerun the query and refresh everything, choose Home Records Refresh Refresh All. This action also shows any new records and hides any that have been changed so that they no longer satisfy your filter conditions.
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.
Renaming a Field in a Query
Tired of seeing long field names in your query results? Based on what you've just learned about expressions, you can painlessly rename a field in your query results. All you need is a calculated field.
The trick's to create a calculated field that matches one of the existing fields (using an expression) and supplies a new name. Technically, you aren't performing any calculation here, but it still works perfectly well. Here's an example of a calculated field that renames DateCustomerPlacedPurchaseOrder to Date:
Date: DateCustomerPlacedPurchaseOrder
The new name (in this example, Date) is known as an alias.
When using this technique, remember not to include the original field (in this case, DateCustomerPlacedPurchaseOrder) in your query. The calculated field (Date) already shows the information you want.