Store a calculated value?

[Jman883]

I was once told that Access does not like to store calculated values but I would like to.

Here is my expression: =([Shift Lenght]-[Total idle time]-[Total down time])/([Shift Lenght]-[Total idle time])*(0.33*[Total pc])/([Shift Lenght]-[Total idle time]-[Total down time])*([Total pc]-[Total scrap])/([Total pc]+0.1)*100

This gives me the value that I desire, now I need to store it in the table with the rest of the records data.
I am planing on storing 3 -4 expressions like this and then averaging them.
Then building a query to the averages for the day,week,month, and year.

Please be gentle I am not very familiar with access but learning a lot

 

[RuralGuy]

There are exceptions to the "Do not store calculated values" mantra. If you need it for historical reasons such as a tax paid when the tax rate might change. There is also the reason such as yours. The basis for the calculations are not always present and the results are needed in another calculation later on. The point of the mantra is that when *any* of the elements used in the calculation change, there is nothing in Access that will recalculate the results for you. It is up to you to update the total field with another calculation.

 

[Jman883]

Thanks for your reply RuralGuy,
can you tell me how to store the calculated value?

 

[RuralGuy]

The easiest way is to call your calculations from the AfterUpdate event of the controls involved in the calculations and put the results in another field in the table. [YourTotalField1] = ...your calculations

 

[Jman883]

Here is how it is done to all that is interested.
RuralGuy thanks for your help.
This Access stuff is kinda frustrating but really cool when you get it to work correctly.

http://support.microsoft.com/kb/209172

 

[KKilfoil]

I am curious why you would want to store these values, assuming that all of the fields in

Here is my expression: =([Shift Lenght]-[Total idle time]-[Total down time])/([Shift Lenght]-[Total idle time])*(0.33*[Total pc])/([Shift Lenght]-[Total idle time]-[Total down time])*([Total pc]-[Total scrap])/([Total pc]+0.1)*100

are always available.

Couldn't you just calculate these in one or more queries, at the moment when you need them?

I am planing on storing 3 -4 expressions like this and then averaging them.
Then building a query to the averages for the day,week,month, and year.

You can create appropriate summary queries to calculate all those things from the raw data.

Then you can be SURE that the derived values are correct for the data presently in your db!

The BIG downside to storing values is that you then have a frozen number. If ANY of the fields in ANY of the underlying records changes (like you add missing data later, or correct errors after you 'froze' the derived values), you are going to have a very difficult exercise to reconstruct the correct values.

As RuralGuy mentioned, usually the only good reason to store derivable data is to store time-variant information (which technically speaking, is not derivable once data changes over time) or to speed up very slow calculations once the number of records in question becomes very large (such as a calculation of item stock-on-hand in a busy inventory system).

 

[Jman883]

I tried to build a query to get the desired data but I was having troubles. I thought it would be easier if I could store the data and then build a query to show me the data over a period of time. When I started the table and form I only had 4 feilds that I was saving data to

[Total idle time]-[Total down time]&[Total pc]-[Total scrap].

I was thinking that if I was able to figure out how to build the queries to show the data without storing it then the next person that might get into this database to add or change might have a hard time with it. I was told by my managers that we need to keep it as simple as possible and not turn it into some software that nobody can understand.

As I become more familiar with Access, I am sure the best way to do this is as you suggest.

 

[RuralGuy]

The most complicated piece of software can become trivial to understand with the use of comments and documentation.