How do I store calculations from a form to a database table?

[schindy]

My boss and I are stuck trying to figure out how to make our calculations be
stored in our database table. Currently our calculations are in the design
view of the form. We are able to see the anwer to the calculations, but they
are not stored in the database table. What are we doing incorrectly? Is there
some other place or way that we are supposed to do calculations?

Thank you for your help.

 

[Beetle]

The short answer is - don't. In most cases you should not try to store
calculated values in a table. Calculations should be done in queries
or in unbound controls on forms. Storing the value in a table is
rarely necessary.

 

[Rick Brandt]

My boss and I are stuck trying to figure out how to make our calculations be
stored in our database table. Currently our calculations are in the design
view of the form. We are able to see the anwer to the calculations, but they
are not stored in the database table. What are we doing incorrectly? Is there
some other place or way that we are supposed to do calculations?

Thank you for your help.

What you are doing incorrectly is WANTING to store them. Calculated values
should be calculated as needed, not stored.

 

[John W. Vinson]

My boss and I are stuck trying to figure out how to make our calculations be
stored in our database table. Currently our calculations are in the design
view of the form. We are able to see the anwer to the calculations, but they
are not stored in the database table. What are we doing incorrectly? Is there
some other place or way that we are supposed to do calculations?

Thank you for your help.

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.

 

[Ken Sheridan]

What the others have said is true, except in situations where the calculation
is on values which might change over time, but you want the results to
reflect the values at the time the calculation was first made. An example is
an invoice, where you might base the invoice amounts on the unit price of a
product multiplied by the quantity supplied. The unit price would be in a
products table, but would change over time. If the invoice amounts were
computed from the values in the Products table then old invoices would
reflect the current prices, whereas they should reflect the prices at the
time the invoice was raised.

In these circumstances you should either assign the unit price value to a
field in an InvoiceDetails table and compute the gross price on that value in
the form's underlying query or an unbound control on the form, or compute the
gross price and assign that to a field in the InvoiceDetails table. Each
invoice will then respect the original prices. You'll find an example in the
OrderDetails sub form in the sample Northwind database, where the unit price
is looked up and assigned to a field in the AfterUpdate event procedure of
the ProductID control.

Ken Sheridan
Stafford, England

 

[schindy]

Thank you everyone for helping us. We are finally back on track.