Calculated Fields

        11 votes: ****      21,115 views      1 comments
by Allen Browne, 20 April 2005    (All versions of Access)

Microsoft Access Tips for Casual Users

Provided by Allen Browne, allen@allenbrowne.com, 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.


Home Index of tips Top

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Calculated Fields'?


1.

beachy says...

26 May 2008

 
i think that this site is very helpful as i am in the middle of doing prep for an IT SAC that involves access.. there is alot of handy information...

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.