Chapter 16
In This Chapter
Developing an expression
Performing complex calculations
Calculating text fields
Using 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 Commitment table with a DonationCommitment
field and a Donation table with a DonationAmount
field. You may be tempted to add an AmountOwed
field to your Donation table that stores the result of subtracting DonationAmount from DonationCommitment. 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.
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 commitment amount was in the Commitment table; the donation amount was in the Donation table. Therefore, a query to calculate donation amount from commitment amount must include information from both the Commitment and Donation 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:
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.
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 (known as an alias). Keep it short and sweet (say, AmountOwed 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?
Enter your calculation, substituting field names for the actual numbers where necessary.
My AmountOwed calculated field would look something like (well, exactly like) the calculation in Figure 16-1.
You don’t have to use only field names in your calculations. You can also enter formulas with numbers, like this:
NewCommitment: DonationCommitment *1.05
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
In Figure 16-2, the datasheet shows the VolunteerID, FirstName, LastName, DonationCommitment, DonationAmount, and the calculated field AmountOwed for each volunteer.
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.
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.
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:
Figure 16-3 shows an example of a nested calculation in Design view.
Figure 16-4 shows the actual results of the calculations.
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.
At times, you may want to include a value in a formula that doesn’t exist anywhere in your database (for example, the number .25 for a 25-percent next donation 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:
Think of an appropriate name for the value (such as Tax Rate, Last Price, or NextDonatePercent).
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 16-5, entering something like [Enter next donation 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 next donation percentage as decimal]
probably is a value that Access asks for when the query runs, not a normal field.
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.
Run the query.
Access displays a dialog box like the one shown in Figure 16-6.
Enter the prompted information.
For this example, just enter the value of your next donation percentage (as a decimal value). Access does the rest.
Number fields aren’t the only fields you can use in formulas. Access can also use the words stored in text fields.
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 16-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 16-8 shows the results of this query.
Creating calculated fields presents you with two basic challenges:
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 Expression Builder (see Figure 16-9).
Expression Builder has several parts, as shown in Figure 16-10.
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 a Volunteers table field and you’re in a database named Access 2016 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 Volunteers table to see the fields in the Expression Categories list. Double-click the desired field to add it to the expression.
Near the bottom of the Expression Elements list, Access also includes a few items for the Truly Technical Person — including these:
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.
Expression Builder works like a big calculator crossed with a word processor:
Figure 16-12 shows how Expression Builder finds and uses the combo box on the form.
Finally, Figure 16-13 shows the expression in the Criteria row of the Donor Totals Report query. So how does all this work? Open the form and select a volunteer. Run the query, and the query will be limited to just the donations for the volunteer selected on the form. Cool, huh?