Calculate Sum and Write it to a Table

[Guest]

I have three tables, Entries, Jobs and Parts. These three tables are related
with one-to-many relationships, from left to right.
i.e. one entry - many jobs
one job many parts

The parts table include quantity and price as fields. Also, jobs and entries
have a field for total. Additionally, Jobs have Labour as a field as well
(currency).

What I want is to update the Total field in Jobs table with the sum of the
product of Quantity*Price of all the corresponding parts for each job. Then,
I would like the Total field in Entries table to be updated with Labour+Total
of all the jobs that correspond with each entry.

 

[Arvin Meyer [MVP]]

If each of the elements of the calculation are stored, it is unnecessary and
poor database design (violation of Normalization rules) to store the results
of a calculation. That rule is only broken if one of the elements is
volatile (come from another table which may be changed) and history is
required, or if the calculations are complex and take a long time to
complete. The second is rare, I've only used it once or twice in 15 years.

 

[tina]

recommend you don't. storing calculated values in a table violates
normalization rules, and if you google these newsgroups you'll see that the
overwhelming consensus is to store the raw data only and calculate totals as
needed "on the fly".

hth

 

[Guest]

Actually, this has to be done, since the prices are likely to change from
time to time, and I would like to keep a history of the entries and jobs.

 

[tina]

it's up to you - but i would always prefer to store the raw data redundantly
(price at-the-time in the job record, for instance, so it doesn't matter
when the current price changes) and recalculate on the fly, rather than
storing a calculation.

hth

 

[John W. Vinson]

What I want is to update the Total field in Jobs table with the sum of the
product of Quantity*Price of all the corresponding parts for each job. Then,
I would like the Total field in Entries table to be updated with Labour+Total
of all the jobs that correspond with each entry.

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]

 

[Guest]

I'm quite aware of the three negative results you mentioned, since such an
action is against normalisation. However, I need to perform this action since
the prices of the parts change from time to time and i need to have a history
of the old entries, with totals. Therefore, normalisation, in my opinion has
to be sacrificed to meet my needs.

 

[Rick Brandt]

I'm quite aware of the three negative results you mentioned, since
such an action is against normalisation. However, I need to perform
this action since the prices of the parts change from time to time
and i need to have a history of the old entries, with totals.
Therefore, normalisation, in my opinion has to be sacrificed to meet
my needs.

(then save the price with your record, not the total)

 

[Arvin Meyer [MVP]]

As Rich mentioned, save the price. It is more useful anyway. For instance,
by knowing the price at any point in time you can do an analysis of price
changes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com