You probably use expressions in queries and forms to evaluate stored values. For instance, you might use an expression to return an employee’s age, based on birth date. Or you might use an expression to calculate an order’s final cost. Sometimes, it seems like storing the calculated result along with the dependent values is a good idea. One of the questions I receive the most is how to do just that.
You can use a simple VBA event procedure to store calculated values in a table, but just because you can doesn’t mean you should. Storing a calculated value violates a basic database design principle: A change in one field should not effect data in another field. For instance, if you change the number of items in an order or apply a discount later, you must also update the stored value that represents the order’s total cost. Forgetting to do so would corrupt not only that one order, but the entire receivables. Instead of storing a calculated value, it’s better to calculate the value each time you need it.
Like every rule, this one is broken occasionally, if there’s a good reason. For instance, you might store calculated values to create historical data. Or if calculations are slowing things down, you might store the calculated results, temporarily.