Microsoft Office Tutorials and References
In Depth Information
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
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.
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.