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 fi elds) or a group calculation (for example, fi nding the total of the values
in a particular fi 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 fi eld for total amount. You can calculate it, however, because the total amount is
equal to the amount paid plus the current due. A fi eld that can be computed from other
fi elds is called a calculated fi eld . A calculated fi 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 fi eld . If calculations are required, decide on the name
for the fi eld. Assign a name that helps identify the contents of the fi 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 fi eld . Determine how the calculated fi 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 fi eld in a query, you enter a name (alias) for the calculated
fi eld, a colon, and then the expression in one of the columns in the Field row. Any fi 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 fi rst.
The steps on the next page use a calculated fi eld to display the number, name, amount paid, current due, and
the total amount for all clients.
Search JabSto ::




Custom Search