Storing Calcualtions from Form to a table

[Guest]

I have read the various threads and understand that this is not recommended,
but... I have to track the cost of repairing products over a three year
period and I believe the cost in the 1st year will be lower then in the 3rd
year. Note: Each product can have more than one repair performed, with
numerous repairs over the three year period each varyinig in cost. At the
end of the process I would like to have the capability of adding these costs
up to summarize the repair expense for that component. So I think I need
the ability to store these calculations with the appropriate record?
If anyone has ideas on how to accomplish this, it is appreciated.
Thanks!

 

[Al Camp]

Mike,
From what I gather, you have a history of multiple repairs against a
particular product.
You should keep all the costs associated with thos repairs in a table
"associated" to the particular product (via serialno, or productID, or
etc..)
Very basically... over time, you'll capture and save the appropriate
parts cost (may go up... may go down) and labor costs (may go up... may go
down) by date of service.
Later, you can calculate anything you want to know about the service of
that product. Total cost, No of Calls, etc...
And even compare one products amongst themselves... Most Expensive to
Maintain, Most Reliable, etc..

Using this system, it's still not necessary to actually save the
calculated charges. For example... as long as you save LaborCost and Hours,
you can always recalculate TotalCost "on the fly" in any subsequent report,
query, etc.. (LaborCost * Hours = LineTotal) OR in the csae of
Parts...(PartCost * Qty = LineTotal)

hth
Al Camp

 

[Guest]

What Al Camp posted is really what you should do. What I don't quite
understand is whether you want to store the result of a calculation, or the
formula that performs the calculation. If it is the formula, you can put the
code for the formula in a string variable. (strFormula = "(x+3/5)*
[SomeField]" and put that in a field in a table. You can the use the Eval
function to performe the calculation:
lngResult = Eval(strWhereIPutMyFormula)
However, that doesn't make much sense. And, if it is the calculation, there
are so many gotchas that it should not be done. One basic Database
Normalization rule is Never Store Anything You Can Calculate. Also, if a
procedure fails and doesn't update correctly, you could get your calculated
field out of sync with reality. So, with all this philosoply having been
said. Do what All recommends.

 

[Guest]

Thank you Klatuu and Al for your assistance. I think I will look at storing
the information as AL suggested. Thanks again.

What Al Camp posted is really what you should do. What I don't quite
understand is whether you want to store the result of a calculation, or the
formula that performs the calculation. If it is the formula, you can put the
code for the formula in a string variable. (strFormula = "(x+3/5)*
[SomeField]" and put that in a field in a table. You can the use the Eval
function to performe the calculation:
lngResult = Eval(strWhereIPutMyFormula)
However, that doesn't make much sense. And, if it is the calculation, there
are so many gotchas that it should not be done. One basic Database
Normalization rule is Never Store Anything You Can Calculate. Also, if a
procedure fails and doesn't update correctly, you could get your calculated
field out of sync with reality. So, with all this philosoply having been
said. Do what All recommends.

I have read the various threads and understand that this is not recommended,
but... I have to track the cost of repairing products over a three year
period and I believe the cost in the 1st year will be lower then in the 3rd
year. Note: Each product can have more than one repair performed, with
numerous repairs over the three year period each varyinig in cost. At the
end of the process I would like to have the capability of adding these costs
up to summarize the repair expense for that component. So I think I need
the ability to store these calculations with the appropriate record?
If anyone has ideas on how to accomplish this, it is appreciated.
Thanks!