To store a calculation in a field or not to store, that is my question.

[Anthony.DG]

Ok so first off I'm not referring to a calculated field in a table but a calculation made on a form and then storing it to a table. I have been reading here and there about normalization but I'm still fairly new to access and database creation. I'm not sure when you should store a field that calculated and when you should do it on the fly with a query/form.

As of right now I have the fields set up and there to store on the following picture.

tblTicketingDetails -[NetWeight] Is derived from subtracting [TareWeight] from [GrossWeight]. Field [Price] is derived from multiplying [MatPricingPerTon] by [NetWeight].

Now should I store [Netweight] and [Price] or should I just create forms and queries later to derive the information for whatever I need at a later time? Which is more efficient? When should I store calculated data? I have so many questions ik

Please let me know what you think and also let me know if there is any particular information that you might require of me to help me to make a better decision.

 

[pbaldy]

A lot of info here:

http://allenbrowne.com/casu-14.html

If you're using a later version a case could be made for using the new calculated data type.

 

[The_Doc_Man]

In general, the rule used to be that if all the data in the record is time-invariant, then anything you can calculate in a query and know it is accurate means don't store the computed value in the table. However, something changes over time, you get to choose what you store.

For example, if you have base price and discount, you can end up with sales price. Of those three, you only need two to compute the third. But you can surely choose whether you store the discount or the sales price. Therefore, the question is, which one do you use more often? (Probably "sales price" in this case.) Store that and only compute the other item when you need it. But note that if you needed to store some sort of code to late be able to justify that price, then you need all three anyway.

The point is, calculated values in a table are USUALLY not worth the headaches down the road if you EVER have to justify anything later.

 

[Pat Hartman]

Calculated fields in tables are very restrictive so you can't use them for anything fancy anyway. For calculated fields, I think Access redoes the calculation when you save the record but do check. I don't know if the field is updated on the form so you can see the value before it is saved but I hope so. The point though is that it is NOT calculated when the record is accessed so you always see the value from the last time the record was saved.

 

[pbaldy]

The point though is that it is NOT calculated when the record is accessed so you always see the value from the last time the record was saved.

I'll accept that as true but I don't see that it matters. Since as far as I know calculated fields can't refer to other tables or other records within the same table, the base data couldn't have changed between the last time the record was saved and when it is accessed. Certainly in the situation described by Anthony.DG the base data is all within the record. Or am I missing something?

 

[Anthony.DG]

Well thank you all. I've decided to only keep the data that's used to make the calculations and delete the table fields that would store the calculations made on my form. I really appreciate you all taking the time to give some advise. Here's to keeping the db bloating down!