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.

What about Calculated fields in Access 2010?

Access 2010 allows you to put a calculated field into a table, like this:

Just choose Calculated in the data type, and Expression appears below it. Type the expression. Access will then calculate it each time you enter your record.

This may seem simple, but it creates more problems that it solves. You will quickly find that the expressions are limited. You will also find it makes your database useless for anyone who needs to use it in Access 2007. Instead of reading the data, they will get a message like this:

Even more serious, the calculated results are not reliable. If you change the Expression after data has been entered into the table, the existing results may not be updated correctly (through new records are edits are updated), so you cannot rely on the results. A compact/repair does not recalculate, so there is no obvious way to repair the bad results.

Since you cannot index calculated fields anyway, there is no performance benefit to be gained from using them. You are violating fundamental rules of data normalization for no good purpose. We recommend you use queries for calculated fields, just as in previous versions.