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 which can slow 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:
- Add a new field called FullName
- Set the Data Type to Calculated
- Click in the Expression option of the Field Properties
- 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 (the ampersand can be used in this manner in Excel as well).
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”.