The article explains why the users should avoid using calculated fields in the database and instead use Queries.
MS Access allows its users to create calculated columns (field type) in the table design to perform various calculations, but more often it breaks the rules of normalization. It is therefore recommended to use Queries instead of calculated fields. This article will elaborate this point in detail.
Calculated Fields in MS Access
Calculated fields are integral part of the spreadsheet and are designed to help users to develop applications effectively. But as per the normalization rule, it’s not recommended to store values which depend on other fields to avoid confusion and irregularity. Calculated fields, despite being handy and easy to use, should be avoided because they are unable to call user defined functions as they are only programmed to run the built-in functions. Access doesn’t allow calculated fields to include fields from different tables or Queries. Users also need to provide all other parameters to avoid errors. It is also worth consideration that the results of calculated fields are computed only when they are generated at run-time, i.e. at the moment of execution of a query or a report based on a query.
It should be noted that Access versions before MS Access 2010, would not be able to function the tables thus limiting its use. The reliability of Calculated fields is comparatively low as the results hardly gets updated correctly upon changing the Expression after data is entered into the table. These are the main reasons for avoiding calculated fields.
Queries
Queries are a better alternative to Calculated fields because they do not store the value in a table. Queries are helpful in automating data management tasks and revision of changes. Queries can be created using the Query Wizard or in Deign view.
Let’s take an example of two fields, Quantity and UnitCost, where the User needs the expression of Quantity * UnitCost to a different field, ‘Amount.’ So instead of using Calculate fields, user should create a Query based on the table. They can type the expression in the Field row of query design, and it can be written as the following
Amount: [Quantity] * [UnitCost]
It will create another field called Amount, which would be simpler and more efficient. The forms and reports based on this query will treat the calculated fields like any other record.
Storing a Calculated Result
There are also a few cases where users feel the need to store a different value, and some flexible rules. It can however be done with the help of After Update event that would allow the users to override the calculation and enter different value as per their preference.
In MS Access 2010 version, one can choose Calculated Data type which will show the expression. Users can type that expression (say [Quantity]*[Unitprice]), and Access will do the calculations each time a user enters the record. Problem with the feature is that there are limited expressions thus restricting users to use it for real purposes.
As an Access user, you should always be prepared to deal with incidents of data corruption. In case you do encounter such a scenario, do not hesitate to call in a powerful tool like DataNumen Access Repair to fix mdb files.
Author Introduction:
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair sql and excel recovery software products. For more information visit www.datanumen.com
1