Microsoft Office Tutorials and References
In Depth Information
Chapter 2: Letting Queries Do the Math
reports, or macros that use the query automatically have access to the
calculated value. To do the math in a query, you create a calculated field within
the query. Unlike the name of a regular field in a query, a calculated field’s
name doesn’t match any of the field names in the tables; in fact, its value
doesn’t come directly from any field in any table. The calculated field exists
only in the query.
A calculated field starts with a field name, followed by a colon and then an
expression that defines the field’s contents, in this order:
fieldname : expression
fieldname is any name you want to use (provided that it doesn’t match the
name of another field in a table), and expression is a formula that tells the
query how to do the math.
Take a look at Figure 2-1, which shows a query in Design view. The first
four field names in the Query by Example (QBE) grid at the bottom of the
window — OrderID, Product Name, Qty, and Unit Price — are regular
fields that get their values from the Order Details or Products table in the
top pane of the window.
field in a
The last field is a calculated field:
ExtPrice: [Qty]*[Unit Price]
The field name is ExtPrice (short for extended price) . The expression is
[Qty] * [Unit Price], which means “the Qty (quantity) field times the
Unit Price field.”
Figure 2-2 shows the same query as Figure 2-1, but in Datasheet view.