Microsoft Office Tutorials and References

In Depth Information

Calculations

Expression Builder

Access includes a tool to

help you create complex

expressions. If you click

Build on the shortcut

menu (see Figure 2-60 on

the next page), Access

displays the Expression

Builder dialog box. The

dialog box includes an

expression box, operator

buttons, and expression

elements. You use the

expression box to build

the expression. You

can type parts of the

expression directly and

paste operator buttons

and expression elements

into the box. You also

can use functions in

expressions.

If you have determined that a special calculation is required for a query, you then need to

determine whether the calculation is an individual record calculation (for example, adding

the values in two ﬁ elds) or a group calculation (for example, ﬁ nding the total of the values

in a particular ﬁ eld on all the records).

JSP Recruiters may want to know the total amount (amount paid plus current due)

from each client. This would seem to pose a problem because the Client table does not

include a ﬁ eld for total amount. You can calculate it, however, because the total amount is

equal to the amount paid plus the current due. A ﬁ eld that can be computed from other

ﬁ elds is called a
calculated ﬁ eld
. A calculated ﬁ eld is an individual record calculation.

JSP also may want to calculate the average amount paid for the clients of each

recruiter. That is, they want the average for the clients of recruiter 21, the average for the

clients of recruiter 24, and so on. This type of calculation is called a group calculation,

because it involves groups of records. In this example, the clients of recruiter 21 would

form one group, the clients of recruiter 24 would be a second, and the clients of recruiter

27 form a third group.

The following are guidelines related to calculations in queries.

Plan

Ahead

Determine whether calculations are required.

1.
Determine whether calculations are required
. Examine the query or request to see if

there are special calculations to be included. Look for words such as “total,” “sum,”

“compute,” or “calculate.”

2.
Determine a name for the calculated ﬁ eld
. If calculations are required, decide on the name

for the ﬁ eld. Assign a name that helps identify the contents of the ﬁ eld. For example, if you

are adding the cost of a number of items, the name “Total Cost” would be appropriate.

The name, also called an
alias
, becomes the column name when the query is run.

3.
Determine the format for the calculated ﬁ eld
. Determine how the calculated ﬁ eld should

appear. If the calculation involves monetary amounts, you would use the currency format.

If the calculated value contains decimals, determine how many decimal places to display.

To Use a Calculated Field in a Query

If you have determined that you need a calculated ﬁ eld in a query, you enter a name (alias) for the calculated

ﬁ eld, a colon, and then the expression in one of the columns in the Field row. Any ﬁ elds included in the expression

must be enclosed in square brackets [ ]. For the total amount, for example, you will type Total Amount:[Amount

Paid]+[Current Due] as the expression.

You can type the expression directly into the Field row. You will not be able to see the entire entry, however,

because the Field row is not large enough. The preferred way is to select the column in the Field row and then use

the Zoom command on its shortcut menu. When Access displays the Zoom dialog box, you can enter the expression.

You are not restricted to addition in calculations. You can use subtraction (-), multiplication (*), or division (/).

You also can include parentheses in your calculations to indicate which calculations should be done ﬁ rst.

The steps on the next page use a calculated ﬁ eld to display the number, name, amount paid, current due, and

the total amount for all clients.