We are Access Experts. Call Us Today! 773-809-5456

Other Tutorials → Why you should not store calculated information in the tables

The question has been asked many times before and it will be asked many times in the future:
“I have calculations on my form but they aren’t saving to the table and I need them to be saved, why aren’t they saving?”.

The answer is straightforward but there is a higher principle at stake here. The answer is that calculations in controls (formulas set in the control sources of the controls) means that they are not bound to the fields where you would be storing the data. But before you go try changing that, you should be aware of a few things.

  1. It is actually RARE that you would store calculated data in the tables.
  2. If you store calculated data in the tables, your data is at a much higher risk of being INCORRECT and you lose the integrity of your data.

So let’s address each one of those. First off, it is rare that you would store calculated data in the tables. There are exceptions to every rule, of course, but the key thing to know is when you should and shouldn’t store the numbers. If you have the data stored in the table which would be used to calculate the number you want, you should not store the calculation (read up on the rules of Normalization and redundant data). If you have numbers which are date specific, in other words, if you must calculate the data as of today and there is no other way to recreate today at a later time, then you may need to store the calculated data. However, if you can store the parts which make up the final calculation, this is a better thing to do so that you can also see what went into the calculation. Sometimes, for auditing or legal/regulatory purposes, you may have to store the calculation even if you could calculate it at run time.

The second statement I made was regarding data integrity. So, what if someone can get to the table directly and change one, or more, of the pieces of data which makes up the calculation? The inputs and the final output will not be in synch, would it? No, it would not. And so what do you believe? The calculated field or the data that makes up the calculation? What if someone changed the calculated field and not the parts that make it up? Again, we don’t know what is right? The integrity of our data is shot, not reliable. So if you only store the parts which make up the calculation and then calculate the result in a query at run time, you will have the correct calculation, even if the parts have been changed.

So, it is best if at all possible, to just calculate the value in the underlying query, of the form or report, when you need it and not store the value. Also, some people think that storing the calculation is necessary if they are wanting to use a table for something. But the reality is that you can use a QUERY in 99% of the places you would use a table. So it normally does not hold that you need this data in a table.