An Access table with a calculated field

The rules of managing data tell us to store data once and not store data that can be derived from existing data. If you have a field storing the age of a person in a database table is it necessary to also have a field storing that person’s current age? No, because you can calculate the person’s age at any time that you require it.

Rules are, as we know, meant to be broken — but only if there is good cause to do so. The benefit must outweigh the negative effect of doing so.
What is the negative effect? Storing additional data bloats your tables and slows your database down.
The benefit? Your database will be faster as it has less calculating / processing to do each time you need that specific information.
How do you make the decision? You need to consider how often you need the derived information and how expensive the calculation is.

I had just been solving this exact problem using an UPDATE query when I came across this feature in Access: the calculated field type.

In the following Access table, we have (amongst others) FirstName and Surname fields. You will often find you need the full name and end up “creating” it on-the-fly in a Query or Report. Here is an alternative approach:

  1. Add a new field called FullName
  2. Set the Data Type to Calculated
  3. Click in the Expression option of the Field Properties
  4. Use the Expression Builder to build (or simply type) the following expression: [Surname] & ", " & [FirstName]

The expression concatenates the Surname field with a comma and a space and the FirstName field.

Save the database table and switch to Datasheet View

If you create a Form based on this table, you will note that Access cleverly makes the control for the Calculated field type “read-only”.

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.