Microsoft Office Tutorials and References
In Depth Information
Chapter 2: Letting Queries Do the Math
Chapter 2: Letting Queries
Do the Math
In This Chapter
Performing math in queries
Writing expressions
Working with Expression Builder
Performing nonbasic math
Calculating dates and times
Using expressions to manipulate text
Writing expressions that make decisions
Looking for blank fields
Using flexible parameter queries
Calculating totals, subtotals, averages, and the like
If you ever find yourself doing math to figure out what to put in a field,
you made a mistake when designing your table. A table needs only the
raw data — the factual information that can’t be calculated from known
data. A table may contain Qty and Unit Price fields to indicate how
many items of some product were ordered and at what price. Having an
Extended Price or Subtotal field in the table is pointless, though,
because Access is smart enough to determine totals on its own by
multiplying the Qty field by the Unit Price field for you.
Letting Access do the math for you has advantages beyond just saving you
the time of doing the calculation yourself. For one thing, Access can do any
mathematical calculation, no matter how complex, in less time than you
take to blink your eye — and its calculations are always correct. You don’t
need to worry about typing a wrong value in an Extended Price field or
forgetting to change a field storing calculated data after you change the Qty
or Unit Price field. Just let Access do all the math.
Doing Calculations in Queries
Access can do the math for you in queries, forms, reports, and macros. In
many cases, you should do the math in a query. When you do, any forms,
Search JabSto ::




Custom Search