Access® 2007 For Dummies®

Chapter 15. Calculating with Your Data

In This Chapter

• Developing an expression

• Performing complex calculations

• Calculating text fields

• Using the Expression Builder

Efficient database design requires that tables contain only necessary fields. Too many fields can cause a table to load slowly — you won't notice the difference with a few hundred records, but you certainly will with a few hundred thousand. Too many fields can also eat up precious disk space. So what fields are often added to a table's design unnecessarily? Fields that could be generated from calculations on data stored in other fields.

For example, suppose you have a products table with a unit price field and an orders table with number of units ordered field. You may be tempted to add an amount field to your orders table that stores the product of the unit price and the quantity ordered. This is unnecessary because Access can perform these calculations on the fly in what's called a calculated field.

A calculated field takes information from another field in the database and performs some arithmetic to come up with new information. In fact, a calculated field can take data from more than one field and combine information to create an entirely new field if that's what you want. You can perform simple arithmetic, like addition and multiplication, or use Access's built‐in functions, such as Sum and Avg (average), for more difficult calculations. For more on using the built‐in functions, see Chapter 14.

In this chapter, you build ...