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

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 format—with more decimal places and no currency symbol—you 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 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.

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

	Date: DateCustomerPlacedPurchaseOrder

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

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]

	ExtendedDeadline: [DueDate] + 14

	ShippingLag: [ShipDate] - [OrderDate]

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

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

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

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]

Whether you’re using the simplest or the most complicated function, the syntax —the rules for using a function in an expression—is the same. To use a function, simply enter the function name, followed by parentheses. Then, inside the parentheses, put all the information the function needs in order to perform its calculations (if any).

For a good example, consider the handy Round( ) function, which takes a fractional number and then tidies up any unwanted decimal places. Round( ) is a good way to clean up displayed values in a calculated field. You’ll see why Round( ) is useful if you create an expression like this, which discounts prices by five percent:

	SalePrice: [Price] * 0.95

Run a price like $43.97 through this expression, and you wind up with 41.7715 on the other side—which doesn’t look that great on a sales tag. The Round( ) function comes in handy here. Just feed it the unrounded number and the number of decimal places you want to keep:

	SalePrice: Round([Price] * 0.95, 2)

Technically, the Round() function requires two pieces of information, or arguments. The first’s the number you want to round (in this case, it’s the result of the calculation Price * 0.95), and the second’s the number of digits that you want to retain to the right of the decimal place (2). The result: the calculation rounded to two decimal places, or 41.77.

	Speed: Abs([DistanceTravelled] / [TimeTaken])

	Speed: Round(Abs([DistanceTravelled] / [TimeTaken]), 2)

	Speed: Abs(Round([DistanceTravelled] / [TimeTaken], 2))

Functions are a great innovation, but Access just might have too much of a good thing. Access provides a catalog of dozens of different functions tailored for different tasks, some of which are intended for specialized mathematical or statistical operations.

To quickly find the functions you want, Access provides a tool called the Expression Builder. To launch the Expression Builder, follow these steps:

Most Access experts find that the Expression Builder is too clunky to be worth the trouble. But even though the Expression Builder may not be the most effective way to write an expression, it’s a great way to learn about new and mysterious functions, thanks to its built-in function reference. If you find a function that sounds promising but you need more information, select it in the list and then click Help. You’ll be rewarded with a brief summary that explains the purpose of the function and the arguments you need to supply, as shown in Figure 7-6.

Figure 7-6. The reference for the Round( ) function spells out what it does, and explains the two parameters. One parameter—the number of decimal places—is wrapped in square brackets, which means it’s an optional value. Leave it out, and Access rounds to the nearest whole number. You’ll also notice a table of contents on the left that lets you browse to any other Access function and read its description.

Format( ) is one interesting mathematical function, which transforms numbers into text. Format( ) is interesting because the text it creates can be formatted in several different ways, which allows you to control exactly how your numbers are presented.

To understand the difference, think back to the expression you used earlier for discounting product prices:

	SalePrice: [Price] * 0.95

Even if the Price field has the Currency data type, the calculated values in the SalePrice field appear as ordinary numbers (without the currency sign, thousands separator, and so on). So you see 43.2 when you might prefer $43.20.

You can remedy this problem by using the Format( ) function to apply a currency format:

	SalePrice: Format([Price] / 0.95, "Currency")

Now the calculated values include the currency sign. Even better, since currencies are displayed with just two decimal places, you no longer need to clean up fractional values with the Round( ) function.

The trick to using the Format( ) function is knowing what text to supply for the second argument in order to get the result you want. Table 7-2 spells out your options.

The mathematical functions in Access don’t get much respect, because people don’t need them terribly often. You’ve already seen Round( ) and Format( )—the most useful of the bunch—but there are still a few others that Access mavens turn to from time to time in calculated fields. They’re listed in Table 7-3.

So far, all the functions you’ve seen have worked with numeric data. However, there’s still a lot you can do with text. Overall, there are three ways you can manipulate text:

Table 7-4 shows the most common functions people use with text.

Using these functions, you can create a calculated field that shows a portion of a long text value, or changes its capitalization. However, how you can use these functions in a filter expression may not be as obvious. You could create a filter condition that matches part of a text field, instead of the whole thing. Here’s an example of a filter condition that selects records that start with Choco:

	Left([ProductName], 5) = "Choco"

Figure 7-7 shows how you enter this filter condition.

Figure 7-7. The Left( ), Right( ), and Mid( ) functions work in much the same way as the Like keyword (Section 4.3.2.3) to help you match bits and pieces of long text values.

The Len( ) function’s a bit of an oddity. It examines a text value and returns numeric information (in this case, the number of characters in the value, including all spaces, letters, numbers, and special characters). The Len( ) function isn’t too useful in a simple calculated expression, because you’ll rarely be interested in the number of letters in a text value. However, it does let you write some interesting filter conditions, including this one that grabs records with a Description of less than 15 characters (which probably could use some extra information):

	Len(Description) < 15

You’ve already seen how you can use simple addition and subtraction with dates (Section 7.1.2.2). However, you can accomplish a whole lot more with some of Access’s date functions.

Without a doubt, everyone’s favorite date functions are Now( ) and Date( ), which you first saw in Chapter 4 (Section 4.3.2.3). These functions grab the current date and time, or just the current date. You can use these functions to create queries that work with the current year’s worth of orders.

Here’s a filter condition that uses Date( ) to select projects that are past due:

	=<Date()

Add this to the Criteria box for the DueDate field, and you’ll see only those records that have a DueDate that falls on or before today.

Date logic becomes even more powerful when paired with the DatePart( ) function, which extracts part of the information in a date. DatePart( ) can determine the month number or year, letting you ignore other details (like the day number and the time). Using DatePart( ) and Date( ), you can easily write a filter condition like this one, which selects all the orders placed in the current month:

	InStr("Hi There", "he")

	FirstWordProduct: Left([ProductName],
        InStr([ProductName], " " - 1))

	DatePart("m", [DatePlaced])=DatePart("m", Date( ))
	  And DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date( ))

This rather lengthy expression’s actually a combination of two conditions joined by the And keyword. The first condition compares the month of the current date with that of the date stored in the DatePlaced field:

	DatePart("m", [DatePlaced])=DatePart("m", Date())

This expression establishes that they’re the same calendar month, but you also need to make sure it’s the same year:

	DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date())

The trick to using DatePart( ) (and several other date functions) is understanding the concept of date components. As you can see, using the text m with the DatePart( ) functions gets the month number, and using the text yyyy extracts a four-digit year. Table 7-5 shows all your options.

	<(Date( )+1)

	=<Now()

You use the date components with several date functions, including DatePart( ), DateAdd( ), and DateDiff( ). Table 7-6 has these and more useful date-related functions.

Databases have two types of fields: required and optional. Ordinarily, fields are optional (as discussed in Section 4.1.1), which means a sloppy person can leave a lot of blank values. These blank values are called nulls, and you need to handle them carefully.

If you want to write a filter condition that catches null values, simply type this text into the criteria box:

	Is Null

This condition matches any fields that are left blank. Use this on the CustomerID field in the Orders table to find any orders that aren’t linked to a customer. Or ignore unlinked records by reversing the condition, like so:

	Is Not Null

Sometimes, you don’t want to specifically search for (or ignore) null values. Instead, you want to swap those values with something more meaningful to the task at hand. Fortunately, there’s an oddly named tool for just this task: the Nz( ) function.

The Nz( ) function takes two arguments. The first’s a value (usually a query field) that may contain a null value. The second parameter’s the value that you want to show in the query results if Access finds a null value. Here’s an example that uses Nz( ) to convert null values in the Quantity field to 0:

	Nz([Quantity], 0)

Converting to 0 is actually the standard behavior of Nz( ), so you can leave off the second parameter if that’s what you want:

	Nz([Quantity])

At this point, you may not be terribly impressed at the prospect of changing blank values in your datasheet into zeroes. But this function’s a lifesaver if you need to create calculated fields that work with values that could be null. Consider this innocent-seeming example:

	OrderItemCost: [Quantity] * [Price]

This expression runs into trouble if Quantity is null. Nulls have a strange way of spreading, somewhat like an invasive fungus. If you have a null anywhere in a calculation, the result of that calculation is automatically null. In this example, that means the OrderItemCost for that record becomes null. Even worse, if the OrderItemCost enters into another calculation or a subtotal, that too becomes null. Before you know it, your valuable query data turns into a ream of empty cells.

To correct this problem, use the Nz( ) function to clean up any potential nulls in optional fields:

	OrderItemCost: Nz([Quantity]) * Nz([Price])

Finally, you can use Nz( ) to supply a different value altogether. In a text field, you may choose to enter something more descriptive. Here’s an example that displays the text [Not Entered] next to any record that doesn’t include name information:

	Name: Nz ([FirstName] & " " & [LastName],  "[Not Entered]")

The simplest possible totals query adds all the records you select into a single row of results, as shown in Figure 7-8. A more advanced totals query uses grouping to calculate subtotals.

The trick to using grouping properly is remembering that the field you use should have many duplicate values. For example, it’s a good idea to group customers based on the state in which they live. Because a given state has many customers, you’ll end up with meaningful subtotals. However, it’s a bad idea to group them based on their Social Security numbers, because you’ll end up with just as many groups as you have customers. Figure 7-9 shows an example of totals query that uses grouping.

You can use multiple levels of grouping in a totals query by adding more than one field with the Total box set to Group By. However, the results might not be exactly what you expect. Suppose you group a long list of sales records by product and by customer. You’ll end up with a separate group for every customer-and-product combination. Here’s part of the results for a query like this that groups records from the OrderDetails table in the Boutique Fudge database and then sorts them by CustomerID:

Figure 7-9. Top: Here, products are grouped by product category. Bottom: The result: a separate row with the totals for each product category.

This table tells you that customer #10 has spent a total of $432.12 dollars on product #108 across all orders. Customer #10 also spent a total of $16.79 on product #134, $53.30 on product #210, and so on. (You could take the same information and sort it by ProductID to look at the total sales of each product to different customers. You still get the same information, but you can analyze it in a different way.)

This is the result you want—sort of. It lacks nice subtotals. It would be nice to know how much customer #10 spent on each type of product, and how much customer #10 spent in total. But thanks to the rigid tabular structure of the totals query, this result just isn’t possible.

If you want to look at this subgrouped information with subtotals, you have two choices. You can use a crosstab query or a pivot-table query—two advanced summary options that are described in Chapter 9. Or, if you’re really interested in printing out your information, you can generate a report that includes multiple levels of grouping and subtotals, as described in Part Three.

Summary queries are insanely useful when you combine them with table joins (Section 6.3) to get related information out of more than one table. In the Boutique Fudge database, the OrderDetails table stores the individual items in each order. You can group this information (as shown in the previous section) to find top-selling products or customers. However, you see only the customer and product ID values, which isn’t very helpful.

If you throw a join or two into the mix, you can pull in related information from linked tables (like Customers, Products, and Orders) and add it to your results. Figure 7-10 shows an example that groups the OrderDetails table by OrderID to find the total cost of each order. It then sorts the results by CustomerID.

You already know enough to build the query shown in Figure 7-10. Here’s what you need to do: