Microsoft Access Tips for Casual Users

Provided by Allen Browne, November 2003. Updated December 2007

Calculated Fields

How do you get Access to store the result of a calculation?

For example, if you have fields named Quantity and UnitPrice, how do you get Access to write Quantity * UnitPrice to another field called Amount?

The best answer is, "Don't!"

Calculated fields belong in queries, not tables.

Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization. Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.

Ask yourself, "Would it be wrong if my field contained a value different from the calculation?" If you answer Yes, you must not store the field. If the difference would be meaningful, you do need the field.

So, how do you get the calculated field if you do not store it in a table? Use a query:

1. Create a query based on your table.
2. Type your expression into the Field row of the query design grid:
       Amount: [Quantity] * [UnitPrice]

This creates a field named Amount. Any form or report based on this query treats the calculated field like any other, so you can easily sum the results. It is simple, efficient, and foolproof.

Well, almost foolproof: It is possible to have your calculated fields misinterpreted.

You want to store a calculated result anyway?

There are circumstances where storing a calculated result makes sense - typically where you need the flexibility to store a different value sometimes.

Say you charge a construction fee that is normally an additional 10%, but to win some quotes you may want to waive the fee. The calculated field will not work. In this case it makes perfect sense to have a record where the fee is $0 instead of 10%, so you must store this as a field in the table.

To achieve this, use the After Update event of the controls on your form to automatically calculate the fee:

1. Set the After Update property of the Quantity text box to [Event Procedure].
2. Click the Build button (...) beside this. Access opens the Code window.
3. Enter this line between the Private Sub... and End Sub lines:
   

   Private Sub Quantity_AfterUpdate()
       Me.Fee = Round(Me.Quantity * Me.UnitPrice * 0.1, 2)
   End Sub

4. Set the After Update property of the UnitPrice text box to [Event Procedure], and click the Build button.
5. Enter this line:

   Private Sub UnitPrice_AfterUpdate()
       Call Quantity_AfterUpdate
   End Sub

Now whenever the Quantity or UnitPrice changes, Access automatically calculates the new fee, but the user can override the calculation and enter a different fee when necessary.

If you are concerned about how a user could enter the calculated total with this arrangement, see Enter text in calculated controls.