modify value in table in large amount.

[fox]

Hi!
I have a question. I have a large data table like this:
Item# value quantity cost
1 2 3 6
2 .. .. ..
and so on......
Now the quantity value is changed already, but the cost is still old value.
What is the best way to make it recalculate the cost? Use a Form with VB? Or
I shoud write a Module?
Thank you very much.

Fox

 

[KARL DEWEY]

First you should not be storing calculated data as parts of the calculation
are subject to change and then the results will be in error as you are now.
Do to calculations in a query ot form as needed.

Second you need to backup your database.

Third create an update query and use [value] * [quantity] in the Update To
row of the query design view grid.

 

[John W. Vinson]

Hi!
I have a question. I have a large data table like this:
Item# value quantity cost
1 2 3 6
2 .. .. ..
and so on......
Now the quantity value is changed already, but the cost is still old value.
What is the best way to make it recalculate the cost? Use a Form with VB? Or
I shoud write a Module?
Thank you very much.

Fox

The best way to deal with this is to not store the Cost in the table AT ALL.

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.

John W. Vinson [MVP]