MUST Store Calculated Field

[Guest]

I have three fields, all expressed as currency, one is my 'Original Value',
one is my 'Current Value' and one is the 'Profit/Loss'. I want/need my
Profit/Loss field to calculate and populate on my form by taking Original
Value - Current Value. I can do this by creating an expression query that the
field pulls from, but then I don't know how to save the Profit/Loss info in a
table.

I know it is TABOO to save a calculated field, but I very much need to in
this case, and I would need it to update when one of the other two fields
update if necessary?
Can anyone tell me how I can make this happen?
Thanks!!!

 

[Dirk Goldgar]

I have three fields, all expressed as currency, one is my 'Original
Value', one is my 'Current Value' and one is the 'Profit/Loss'. I
want/need my Profit/Loss field to calculate and populate on my form
by taking Original Value - Current Value. I can do this by creating
an expression query that the field pulls from, but then I don't know
how to save the Profit/Loss info in a table.

I know it is TABOO to save a calculated field, but I very much need
to in this case, and I would need it to update when one of the other
two fields update if necessary?
Can anyone tell me how I can make this happen?

It is possible to save a calculated field, but it is not possible to
make that field automatically update whenever the fields it is based on
are updated -- at least not unless you make sure that there is
absolutely no way those fields can be updated except by this form.

What you're asking doesn't really make sense. If the field is the
result of a static calculation, such that it should not be recalculated
if its base values change, then it makes sense to store it. If the
field is the result of a dynamic calculation, such that it should be
recalculated if its base values change, then it makes no sense to store
it.

Can you explain why you feel you need to save the result of a dynamic
calculation? I can tell you how to save the result of a calculation,
but I don't want to tell you to do something that's going to cause you
no end of troubles later on.

 

[Rick B]

Storing it to the table would be possible, but you don't explain why you
need to. Unless the "current value" changes, there would be no reason to
store it. If the current value changes and you do want to store it, then
you would NOT want to update it. In other words, if you want the value to
update based on current information, then you DON'T store it, you just
calculate it when you need it.

Even if you do need to store the value "at this moment", you can do so by
storing the 'current value' and the 'original value' then doing the math in
your forms or reports. The key there is that you are storing the 'current
value' in the detail table. If the prices fluctuate, you would have the
snapshot price in your table. You would not, however, need to calculate
profit/loss, since you would have the data needed to calculate it on the
fly.




Rick B

 

[John Vinson]

I have three fields, all expressed as currency, one is my 'Original Value',
one is my 'Current Value' and one is the 'Profit/Loss'. I want/need my
Profit/Loss field to calculate and populate on my form by taking Original
Value - Current Value. I can do this by creating an expression query that the
field pulls from, but then I don't know how to save the Profit/Loss info in a
table.

I know it is TABOO to save a calculated field, but I very much need to in
this case, and I would need it to update when one of the other two fields
update if necessary?
Can anyone tell me how I can make this happen?
Thanks!!!

I'll just have to chime in with Rick and Dirk that it is almost
certainly NOT necessary to store this value. Here's my boilerplate
response:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you REALLY REALLY need to store it - knowing full well that the
value in Profit/Loss *MAY VERY WELL BE WRONG*, and in fact will
*certainly* be wrong if any one of the three fields is edited - then
you can use the AfterUpdate events of the [Original Value] and
[Current Value] controls to "push" the result into [Profit/Loss]. Do
you want to store wrong data? If so, post back explaining why, and
I'll hold my nose and post the code...


John W. Vinson[MVP]

 

[Norman Yuan]

I strongly agree with the other posts.

But just my guess. You probably want to keep tracks of the profit/loss
changes, due to the changes of other two values. In this case, there could
be multiple values of Profit/Loss whenever the other value(s) changed. That
is, there is a One-To-Many relationship, so you should put the calculated
value in another table linked to the table where the two values are stored
with a foreign key. Other than this, I really couldn't think of any reason
the store the calculated value (absolutely not in the same table as the
other two values).

 

[Joseph Meehan]

I have three fields, all expressed as currency, one is my 'Original
Value', one is my 'Current Value' and one is the 'Profit/Loss'. I
want/need my Profit/Loss field to calculate and populate on my form
by taking Original Value - Current Value. I can do this by creating
an expression query that the field pulls from, but then I don't know
how to save the Profit/Loss info in a table.

I know it is TABOO to save a calculated field, but I very much need
to in this case, and I would need it to update when one of the other
two fields update if necessary?
Can anyone tell me how I can make this happen?
Thanks!!!

Your dilemma is one of the reasons computed values should not be stored
in a table.

 

[Pedro]

I really needed to store a calculated field too.
This pointed me in the right direction:

You want to store a calculated result anyway?
There are circumstances where storing a calculated result makes sense -
typically where you need the flexibility to store a different value
sometimes.

Say you charge a construction fee that is normally an additional 10%, but to
win some quotes you may want to waive the fee. The calculated field will not
work. In this case it makes perfect sense to have a record where the fee is
$0 instead of 10%, so you must store this as a field in the table.

To achieve this, use the After Update event of the controls on your form to
automatically calculate the fee:

1 Set the After Update property of the Quantity text box to [Event
Procedure].
2 Click the Build button (...) beside this. Access opens the Code window.
3 Enter this line between the Private Sub... and End Sub lines:

Private Sub Quantity_AfterUpdate()
Me.Fee = Round(Me.Quantity * Me.UnitPrice * 0.1, 2)
End Sub
4 Set the After Update property of the UnitPrice text box to [Event
Procedure], and click the Build button.
5 Enter this line:
Private Sub UnitPrice_AfterUpdate()
Call Quantity_AfterUpdate
End Sub
Now whenever the Quantity or UnitPrice changes, Access automatically
calculates the new fee, but the user can override the calculation and enter
a different fee when necessary.

Pedro