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:
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).
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 220.127.116.11) 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 .
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.
Before going any further, it's worth reviewing the rules of calculated fields. Here are some pointers:
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]
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.
18.104.22.168. 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.
22.214.171.124. 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:
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]