Data Management

Step-By-Step: Calculate totals in Access on the fly

Instead of wasting disk space with stored values, you should use calculated fields whenever possible in a database. Simply follow these steps to create your own calculated fields for queries.

Here's one rule of database management that rings true no matter which database software you use: Don't store a value that you can create on the fly when you need it. In other words, you can save disk space by using calculated fields whenever possible.

Let's look at an example. Say you frequently query an Orders table that contains a Quantity field and a Unit Price field, among others. You don't want to store the total price (the quantity times the unit price) for two reasons. First, if someone edits the record and the price or quantity changes, you'll have to recalculate the price and rewrite the new value to disk. Second, it's easier to display the total price on the fly. Creating a calculated field in a query is pretty straightforward:
  1. Create a new query in Design view.
  2. Add at least the Quantity and Unit Price fields.
  3. Click the Field row of a blank column on the query design grid.
  4. Type the name of the calculated field, type a colon, and then enter the expression that defines the calculated value.

For instance, you might enter Total Price:[Quantity]*[Unit Price] as shown in Figure A. Be sure to select the Show check box for this calculated field. When you execute the query, Access will display the results of the calculation in the results.

Figure A

You can perform all sorts of calculations in a query using calculated fields. You can even add criteria to calculated fields. For example, in the above scenario, you could limit the query output to records whose total price is greater than 500 by adding >500 to the Criteria row under the calculated field.

What do you think of this article format?
Our editors are developing and refining new methods and formats for delivering the solutions you need. Do shorter, step-by-step pieces such as this one help you overcome the IT problems that keep you up at night? Share your thoughts with our editors; you could win a free TechRepublic book or CD of your choice. Content for this article was derived from the TechRepublic Microsoft Office e-newsletter. You can have tips like this delivered via e-mail each week by signing up here.

Editor's Picks