Increment Total on Second Table from Form

  • Thread starter Thread starter Paul Gurwitz
  • Start date Start date
P

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
 
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
 
Back
Top