Increment Total on Second Table from Form

[Paul Gurwitz]

I have two one-to-many related tables, "Jobs" and "Charges". Jobs has a
primary key called "Jobnum". Charges has a number of records relating
to each Jobnum; these records include information about specific
charges, including the amount, called "Charge". Jobs has a field called
"Totcharge", which is the sum of Charges for all Charges in each Jobnum.

I have designed a form to input a new Charge record. When I click the
command button after having input the data, I would like to increment
Totcharge on the Jobnum record of Jobs by the amount of Charge.

What is the simplest way of doing this in Access?

Thanks
Paul

 

[Pavel Romashkin]

The answer is, do not keep the running total in the table at all. You
don't need to. All you need is a GROUP BY query on the Charges table:

SELECT Sum([Charge]) FROM Charges GROUP BY Jobnum

and you are done. No table updates necessary, and your running sum is
always current.
(technically, storing a running sum in a table violates a normal form
that states, fields in a table should not be totally dependent on other
any other fields). This can be rephrased "if you can calculate
something, don't store it, it belongs in a query".

Good luck,
Pavel