Update with calculation

[Susan L]

How do I update a table with a calculation performed in a
form? I enter the data in the form, have a field for the
total, have the correct calculation showing in the form,
but the field in the table stays at 0. I would like the
calculation to be permanently saved in the table.
Thanks in advance

 

[Lynn Trapp]

Is there a reason that you need the calculation stored in the table? If you
can calculate it on the fly for the form, as you have apparently done, then
you can calculate it on the fly anytime you need it -- query, report, etc.

 

[Susan L]

I'm trying to avoid having to enter the formula every time
I prepare a report etc. Just have the number even in the
table. Is it possible?

 

[Marin Kostov]

The calculations made on form or report are the best way of storing
Calculated data. No, you cannot store the calculated number in the table.
Why?
Just because one control can have only ONE record source. If your record
source is a Table or Query, that control will be Bound to that Table or
Query. And if your control is calculated (in run-time), the Record Source
will be something like:
=[Quantity] * [Price]
When you save the Report, the record source Property of the control (the
formula) is saved too. So the next time you will not need to re-write te
formula. It is allready there!
And another good reason for using calculated controls - thedata is NOT
stored in the Table - it will NOT use disk space. Just thing about:
You have 5000 records, and when you prefrorm calculations, the data is
stored in the table... It will GROW and loose perfomance...

 

[Lynn Trapp]

Then put the calculation in a saved query that you use as the record source
for your different reports. You only have to enter the calculation once.

 

[Lynn Trapp]

Susan,
Let me try to clarify something for you. While it is possible to store the
calculated value in your table, in the end it will cause you more headaches
than you are probably wanting to go through. Mainly, that you will have to
find some mechanism for updating the calculated field everytime anyone makes
a change to the fields it is based on. When you find that mechanism, you
have, basically, don't what I have suggested in calculating the field on the
fly, as needed.

 

[John Vinson]

I'm trying to avoid having to enter the formula every time
I prepare a report etc. Just have the number even in the
table. Is it possible?

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 do the
calculation in a Query, you can use that query as the recordsource for
any number of reports - you don't need to "redo" it every time.