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

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

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.

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 .

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.

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

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]

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.

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:

1. Parentheses (Access always performs any calculations within parentheses first)

2. Percent

3. Exponents

4. Division and multiplication

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

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]

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]