Microsoft Access Database: Should We Be Using Calculated Table Fields
With the introduction of Microsoft Access 2010, a new data type field appeared in your table design view which provided a basic way to take fields in the same table and create new expressions as a new dedicated field sitting at the ‘top of hierarchy’ database!
However, should we really be using this new data type and practice at all?
Purists will frown upon this technique and say you should stick with Excel to store real values and expressions together. By doing so, you are breaking the rules of database normalisation and also cause a maintenance headache once your database has been deployed.
There are some considerations to think about that may help decide if you should or shouldn’t use your tables to store non-real values (expressions).
Should We Be Using Calculated Table Fields
By carrying out a quick set of questions in this suggested order, it may help determine what the best approach should be. If you answer ‘No’ to the question, then stop the test and do not continue – you should not be using this approach!
1. What’s the the scope and re-usability for an expression based output value? If you believe this calculated expression is going to be used across many different objects like queries, forms and reports then answer ‘Yes’.
2. Will you never require to re-edit or change an expression? Think about an expression like a full contact name which may be a concatenation of three fields (Title, First name and Last name) and that this is a standard that may never change compared to an expression that may have a fixed tax rate like [Net Total] * 20%.
3. Will your Microsoft Access database application be downgraded to an earlier version like 2007? As you can imagine, earlier versions do not have such a data type and will cause errors.
4. Do you need to keep your database as optimised as possible and ensure the performance levels are in tip top condition due to have a large volume of records to manage? Since a calculated field can not be indexed you may experience performance issues for large volumes of data as it needs to refresh and recalculate when the table is being called and the application may slow down.
In the true sense when using a calculated field, we should really stick to the traditions of MS Access and create expressions in all other objects other than a table based on re-usability and scope.
The Query should be the top level for an expression allowing you to scope this across your forms and reports. If you are going to use an expression as a one-off then stick into the actually calling object; the form or report.
So, you should know my point of view on this matter but as I keep preaching to my students, there’s no absolute right or wrong way, there are many ways – so you decide!