Microsoft Office Tutorials and References
In Depth Information
Chapter 2: Letting Queries Do the Math
Figure 2-1 in
Notice two things about this Datasheet view:
✦ The ExtPrice field looks just like any other field.
The value shown in the ExtPrice column is equal to the value of the
Qty field times the Unit Price field in each column.
Even though the ExtPrice column in Datasheet view looks like a regular
field, it doesn’t behave exactly like a regular field. If you try to change the
contents of the ExtPrice field, Access won’t let you. The contents of the
ExtPrice field in this query always show the quantity times the unit price
and can’t possibly show anything else, because ExtPrice is a calculated
If you change the Qty or Unit Price field in any record, however, the
ExtPrice field instantly — and automatically — changes to show the
correct result based on the change you make. If you change the Qty field in the
first record in Figure 2-2 from 1 to 2, the ExtPrice field for that record also
changes to show $200.00.
Follow these steps to create calculated fields in queries:
1. Create a normal select query, like any of those shown in Book III,
2. To the Field row of the QBE grid, add any fields that you want the
query to display.
3. To add a calculated field, pick any empty column, and type a unique
new field name in the Field row, followed by a colon ( ) and an :
expression that performs the calculation.
What you get looks a lot like the ExtPrice calculated field shown in
Figure 2-1, earlier in this chapter.