Chapter 15

Express Yourself with Formulas

In This Chapter

Developing an expression

Performing complex calculations

Calculating text fields

Using the Expression Builder

Efficient database design requires that tables contain only necessary fields. Too many fields can cause a table to load slowly — you won’t notice the difference with a few hundred records, but you certainly will with a few hundred thousand. So what fields are often added unnecessarily to a table’s design? The short answer: fields that could be generated from calculations on data stored in other fields.

For example, suppose you have a Products table with a UnitPrice field and an OrderDetails table with a Quantity field. You may be tempted to add an Amount field to your OrderDetails table that stores the result of multiplying UnitPrice times Quantity. This is unnecessary because Access can perform these calculations on the fly — in what’s called a calculated field.

A calculated field takes information from another field or fields in the database and performs some arithmetic to come up with new information. Access calls the arithmetic formula used to perform the calculation an expression. In fact, a calculated field can take data from more than one field and combine information to create an entirely new field if that’s what you want. You can perform simple arithmetic (such as addition and multiplication) or use Access’s built-in functions, such as DSum and DAvg (average), for more difficult calculations.

In this chapter, you build all kinds of calculations into your queries. From simple sums to complex equations, the information you need is right here.

Although the examples in this chapter deal with calculated fields in queries, the same concept applies to calculated fields in forms and reports.

A Simple Calculation

The first step in creating a calculated field in a query is to include the tables that contain the fields you need for your calculation. In the preceding example, the product unit price was in the Products table; the quantity ordered was in the OrderDetails table. Therefore, a query to calculate unit price multiplied by quantity ordered must include information from both the Products and OrderDetails tables. Access can’t pull the numbers out of thin air for the calculation; you must make sure the fields that contain the numbers you need are present in your query.

Access uses a special syntax for building calculated fields. Here’s how to create a calculated field:

1. Click an empty column in the Field row of the query grid.

The good old cursor will blink in the row. Access puts the results of the calculation in the same grid position as the calculation itself, so if the calculation sits in the third column of your query grid, the calculation’s result will be in the third column, too.

2. Enter a name for your calculation followed by a colon (:).

Access will refer to this calculation from now on by whatever name you enter before the colon. Keep it short and sweet (say, Amount or Tax) so it’s easier to refer to later on. If you don’t name your calculation, Access will put the generic Expr (followed by a number) as its name. It has to be called something, so why not Expr1 or Expr2, right?

3. Enter your calculation, substituting field names for the actual numbers where necessary.

My Amount calculated field would look something like (well, exactly like) the calculation in Figure 15-1.

You don’t have to use only field names in your calculations. You can also enter formulas with numbers, like this:

Tax: Quantity * UnitPrice * .06

If a field name contains more than one word, put square brackets around it. Access treats anything else it finds in the calculation as a constant (which is the math term for it is what it is and it never changes). If the field name contains no spaces, Access will put the square brackets in for you after you enter the field name. That’s why I always use one-word field names — so I don’t have to type those irritating square brackets.

When you create formulas, keep these general guidelines in mind:

You must type the field names and constants into your formula. You can’t just drag and drop stuff from the table list.

Don’t worry if your calculation grows past the edge of the Field box. Access still remembers everything in your formula, even if it doesn’t appear onscreen.

To make the query column wider, aim the mouse pointer at the line on the right side of the thin bar above the calculation entry. Keep trying until you see a vertical line with a horizontal double arrow through it. When that happens, click and drag the mouse to the right. As you do, the column expands according to your movements. To fit the width to just the right size, position the mouse to size the column as described earlier — and then double-click! Isn’t Access a gem?

If it’s a super-duper long calculation, press Shift+F2 while the cursor is somewhere on the calculation.

This opens the Zoom dialog box so you can easily see and edit everything in a pop-up window.

When you run a query containing a calculation, Access

Produces a datasheet showing the fields you specified.

Adds a new column for each calculated field.

In Figure 15-2, the datasheet shows the Company Name, Product, Unit Price, Quantity, and the calculated field Amount for each item ordered.

Figure 15-1: The Amount calculation.

Figure 15-2: The results of the amount calculation in Datasheet view.

Complex Calculations

After getting the hang of simple calculations, you can easily expand your repertoire into more powerful operations, such as using multiple calculations and building expressions that use values from other calculations in the same query. This stuff really adds to the flexibility and power of queries.

Calculate ’til you drop!

Access makes it easy to put multiple separate calculations into a single query. After building the first calculation, just repeat the process in the next empty Field box. Keep inserting calculations across the query grid until you’ve calculated everything you need.

You can use the same field in several calculations. Access doesn’t mind at all.

Using one calculation in another

One of the most powerful calculated-field tricks involves using the solution from one calculated field as part of another calculation in the same query. This is sometimes called a nested calculation, and it can do two useful tricks:

It creates a field in the query results.

It supplies data to other calculations in the same query, just as if it were a real field in the table.

Figure 15-3 shows an example of a nested calculation in Design view.

Figure 15-4 shows the actual results of the calculations.

Although this technique seems simple, be careful. A small error in one calculation can quickly trickle down to other calculations that are based on it, compounding a simple error into a huge mistake. Yikes!

Figure 15-3: The Amount Calculated column is referred to in the Tax Calculated column.

Figure 15-4: The results of the Amount Calculated and Tax Calculated columns.

To use the results from one calculation as part of another, just use the name of the first calculation as if it were a field name. In short, treat the first calculation like a field in your table.

---

---

Using parameter queries to ask for help

At times, you may want to include a value in a formula that doesn’t exist anywhere in your database (for example, the number .06 for a 6-percent tax rate in the calculation example earlier in this chapter). If you know the value, you can type it directly into the formula.

But what if the number changes all the time? You don’t want to constantly alter a query; that’s a big waste of time and effort. Instead of building the ever-changing number into your formula, why not make Access ask you for the number (called a parameter) when you run the query? This is an easy one:

1. Think of an appropriate name for the value (such as Tax Rate, Last Price, or Discount).

When choosing a name for your parameter, don’t use the name of an existing field in your table — Access will ignore the parameter if you do. Instead, go with something that describes the number or value itself. As you can see in the query grid shown in Figure 15-5, entering something like [Enter discount percentage as decimal] makes it very clear what is required from the user. When you look at this query months (or even years) from now, you can easily recognize that something called [Enter discount percentage as decimal] probably is a value that Access asks for when the query runs, not a normal field.

Figure 15-5: The discount parameter in the DiscountAmount calculated field.

2. Use the name in your formula as if it were a regular field.

Put square brackets around it and place it into your calculation, just as you did with the other fields.

3. Run the query.

Access displays a dialog box like the one shown in Figure 15-6.

Figure 15-6: Access asks for a discount.

4. Enter the prompted information.

For this example, just enter the value of your discount (as a decimal value). Access does the rest.

This option means you can use the same query with different values to see how changing that value affects your results.

“Adding” words with text formulas

Number fields aren’t the only fields you can use in formulas. Access can also use the words stored in text fields.

A classic formula comes from working with names. If you have a Contacts table with FirstName and LastName fields, you will at some point want to string those names together on a form or report. A text formula can do this for you. It can add the first name to the last name; the result is the person’s full name in one column.

The syntax for text-field formulas is similar to the syntax for number-field formulas — the field name is still surrounded by square brackets and must be carefully entered by hand. Include literal text in the formula (such as spaces or punctuation) by surrounding it with quotation marks (for example, you’d type “,” to insert a comma).

You connect text fields with the ampersand character (&). Microsoft calls the ampersand the concatenation operator, which is a fancy way to say that it connects things.

Figure 15-7 shows a text-field formula. This example solves the problem I describe earlier in this section: making one name out of the pieces in two separate fields. The formula shown in the figure combines the FirstName and LastName fields into a single full name, ready to appear on a mailing label, report, or some other useful purpose you dream up.

This formula consists of the FirstName field, an ampersand (&), a single space inside quotation marks, followed by another ampersand, and then the LastName field. Here’s what it looks like:

[FirstName]&“ ”&[LastName]

When you run this query, Access takes the information from the two fields and puts them together, inserting the space between them so they don’t run straight into each other. Figure 15-8 shows the results of this query.

Figure 15-7: Turning two names into a single calculated field.

Figure 15-8: First and last names are combined to the single field, FullName.

Hooray for the Expression Builder

Creating calculated fields presents you with two basic challenges:

Figuring out what the formula should say

Entering the formula in a way that Access recognizes

Although Access can’t help you with the first problem, it tries hard to offer some assistance with the second. When all else fails, and you just can’t assemble a calculated field in query design exactly the way Access wants it, click the Builder button on the Design tab of the Ribbon to open the Expression Builder (see Figure 15-9).

The Expression Builder has several parts, as shown in Figure 15-10.

You create the expression in the big window at the top.

The lower half of the dialog box contains three lists that work as a team:

Figure 15-9: The Builder button launches the Expression Builder.

Figure 15-10: Expression Builder in all its glory.

• The Expression Elements list provides a navigational tree containing all the items that are at your disposal to build an expression such as tables, queries, forms, reports, constants, functions, and operators.

You’ll find the tables, queries, forms, and report objects within your database under an Expression Element with the same name as the name of your database. For example, if you want to select an Orders table field and you’re in a database named Access 2013 Dummies.accdb, you’ll see an item with that name on the Expression Elements list. Expand that item by clicking the plus sign (+) next to it to see the Tables item. Expand the Tables item to see the list of tables. Click the Orders table to see the fields in the Expression Categories list. Double-click the desired field to add it to the expression.

• When you click something in the Expression Elements list (such as Operators), the Expression Categories list populates with the categories within the selected element.

---

---

• When you click a category in the Expression Categories list (such as Comparison), the Expression Values list populates with the items within the selected category that finally (whew!) you can use in your expression.

  Near the bottom of the Expression Elements list, Access also includes a few items for the Truly Technical Person — including these:

• Constants (values that never change, such as true and false).

• Operators that are available for comparisons and formulas:

The Arithmetic category contains operators for addition, subtraction, division, and multiplication.

The Comparison category contains operators such as =, >, <, <>

Use comparisons to develop expressions for the Criteria section of your queries, when you need a response of True or False.

The Logical category contains logical operators such as And, Or, and Not.

The String category contains the Ampersand (&) operator that is used to combine two text fields.

• A folder called Common Expressions that contains stuff that’s common only if you’re creating reports or working with dates.

Expression Builder works like a big calculator crossed with a word processor:

At the bottom, double-click items in the Expression Categories or the Expression Values list (depending on the situation) to include them in your expression at the top.

Click anywhere in the big pane on top; then type whatever you need to type.

Expression Builder can refer to controls on a form in your query so you can easily control the criteria used to run the query. Figure 15-11, for example, shows a simple form called Order Report with one combo box called Customer that lists all customers in the Customers table.

Figure 15-12 shows how the Expression Builder finds and uses the combo box on the form.

Finally, Figure 15-13 shows the expression in the Criteria row of the Order Report query. So how does all this work? Open the form and select a customer. Run the query, and the query will be limited to just the orders for the customer selected on the form. Cool, huh?

Figure 15-11: The Order Report form with the Customer combo box.

Figure 15-12: The Expression Builder is used to select the Company Name combo box from the Order Report form.

Figure 15-13: The expression as it appears in the Criteria row of the Order Report query.