Section 7.1. Calculated Fields | Access 2007[c] The Missing Manual

7.1. 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.

7.1.1. 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 (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.)

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 (see Figure 7-1).

Figure 7-1. 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 7-2). 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 (Section 7.2.1.1) and formatting (Section 7.2.3) features discussed later in this chapter.

Figure 7-2. 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.

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 described in Section 7.3.

UP TO SPEED
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 (as described in Chapter 18), 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.

GEM IN THE ROUGH
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.

7.1.2. Simple Math with Numeric Fields

Many calculated fields rely entirely on ordinary high school math. Table 7-1 gives a quick overview of your basic options for combining numbers.

Table 7-1. Arithmetic Operators

Operator	Name	Example	Result
+	Addition	1+1	2
-	Subtraction	1-1
*	Multiplication	2*2	4
^	Exponentiation	2^3	8
/	Division	5/2	2.5
\	Integer division (returns the lowest whole number and discards the remainder)	5\2	2
Mod	Modulus (returns the remainder left after division)	5 Mod 2	1

You're free to use as many fields and operators as you need to create your expression. Consider a Products table with a QuantityInStock field that records the number of units in your warehouse. To determine the value you have on hand for a given product, you can write this expression that uses two fields:

 ValueInStock: [UnitsInStock] * [Price]

Tip: When performing a mathematical operation with a field, you'll run into trouble if the field contains a blank value. To correct the problem, you need the Nz( ) function, which is described in Section 7.3.

7.1.2.1. Date fields

You can also use the addition and subtraction operators with date fields. (You can use multiplication, division, and everything else, but it doesn't have any realistic meaning.)

Using addition, you can add an ordinary number to a date field. This number moves the date forward by that many days. Here's an example that adds two weeks of headroom to a company deadline:

 ExtendedDeadline: [DueDate] + 14

If you use this calculation with the date January 10, 2007, the new date becomes January 24, 2007.

Using subtraction, you can find the number of days between any two dates. Here's how you calculate how long it was between the time an order was placed and when it was shipped:

 ShippingLag: [ShipDate] - [OrderDate]

If the ship date occurred 12 days after the order date, you'd see a value of 12.

Note: Date fields can include time information. In calculations, the time information's represented as the fractional part of the value. If you subtract two dates and wind up with the number 12.25, that represents 12 days and six hours (because six hours is 25 percent of a full day).

Remember, if you want to include literal dates in your queries (specific dates you supply), you need to bracket them with the # character and use Month/Day/Year format. Here's an example that uses that approach to count the number of days between the date students were expected to submit an assignment (March 20, 2007) and the date they actually did:

 LateDays: [DateSubmitted] - #03/20/07#

A positive value indicates that the value in DateSubmitted is larger (more recent) than the deadline datein other words, the student was late. A value of 4 indicates a student that's four days off the mark, while 4 indicates a student that handed the work in four days ahead of schedule.

7.1.2.2. Order of operations

If you have a long string of calculations, Access follows the standard rules for order of operations : mathematician -speak for deciding which calculation to perform first when there's more than one calculation in an expression. So if you have a lengthy expression, Access doesn't just carry on from left to right. Instead, it evaluates the expression piece by piece in this order:

Parentheses (Access always performs any calculations within parentheses first)
Percent
Exponents
Division and multiplication
Addition and subtraction

Suppose you want to take the QuantityInStock and the QuantityOnOrder fields into consideration to determine the value of all the product you have available and on the way. If you're not aware of the order of operation rules, then you might try this expression:

 TotalValue: [UnitsInStock] + [UnitsOnOrder] * [Price]

The problem here is that Access multiplies QuantityOnOrder and Price together, and then adds it to the QuantityInStock. To correct this oversight, you need parentheses like so:

 TotalValue: ([UnitsInStock] + [UnitsOnOrder]) * [Price]

Now the QuantityInStock and QuantityOnOrder fields are totaled together, and then multiplied with the Price to get a grand total.

Tip: Need some more space to write a really long expression? You can widen any column in the query designer to see more at once, but you'll still have trouble with complex calculations. Better to click in the Field box, and then press Shift+F2. This action pops open a dialog box named Zoom, which shows the full content in a large text box, wrapped over as many lines as necessary. When you've finished reviewing or editing your expression, click OK to close the Zoom box and keep any changes you've made, or Cancel to discard them.

7.1.3. Expressions with Text

Although calculated fields usually deal with numeric information, they don't always. You have genuinely useful ways to manipulate text as well.

If you have text information, then you obviously can't use addition, subtraction, and other mathematical operations. However, you can join text together. You can, for instance, link several fields of address information together and show them all in one field, conserving space (and possibly making it easier to export the information to another program).

To join text, you use the ampersand (&) operator. For example, here's how to create a FullName field that draws information from the FirstName and LastName fields:

 FullName: [FirstName] & [LastName]

This expression looks reasonable enough, but it's actually got a flaw. Since you haven't added any spaces, the first and last name end up crammed together, like this: BenJenks . A better approach is to join together three pieces of text: the first name, a space, and the last name. Here's the revised expression:

 FullName: [FirstName] &" "& [LastName]

This produces values like Ben Jenks . You can also swap the order and add a comma, if you prefer to have the last name first (like Jenks, Ben ) for better sorting:

 FullName: [LastName] & ", " & [FirstName]

Note: Access has two types of text values: those you draw from other fields, and those you enter directly (or hard-code ). When you hard-code a piece of text (such as the comma and space in the previous example), you need to wrap it in quotation marks so Access knows where it starts and stops.

You can even use the ampersand to tack text alongside numeric values. If you want the slightly useless text "The price is" to appear before each price value, use this calculated field:

 Price: "The price is: " & [Price]