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,