Header Record Fields Updated from Calculated Details

• junk 84169

  Grasshopper

  Points: 19

  More actions

  January 26, 2010 at 1:16 pm

  #219975

  Ok, let's say you have a simple setup with some invoice header records and some related details.

  What's the best way to have an invoice total field on the header, which is the sum of all the details?

  And let's say you also have a bunch of payment details for each invoice and when sum of the payments

  reach the total, the invoice is considered closed or IsPayed in full.

  Of course you want to be able to easily query only the open invoices.

  I know how to make do this with calculated fields on the header, where the sum is calculated by doing

  a sub-select on the sum of all the detail records, likewise for the sum of the payments and with a third

  to do the if payments>=total than IsPayed = true.

  The problem is that this method is very, very slow when you get tons of records, with the system basically

  summing up the entire table of details every time you query for open invoices.

  I'm sure there is another method whereby triggers or code is updating the header only when one of the details

  has changed so the totals and IsPayed is readily available.

  Can someone point me to a good example for SQL server - and can this be done in Access as well?

  Thanks in advance, Scott

• DanielG-259128

  SSC Veteran

  Points: 291

  More actions

  January 27, 2010 at 2:02 pm

  #1109585

  You could try to add a trigger on the items that updates the header total

  *by adding the entire item total when item records get inserted, and

  * adding the difference between the old and new values when the items get updated

  I'd reccomend periodically updating the header value with the sum of all item values ... perhaps as part of a batch maintenance process.

  SO.

  Header record for ABC001 has a value of 0

  then

  Insert ABC001 item 1 with value of 15, then add 15 to 0 on header

  Insert ABC001 item 2 with value of 5, then add 5 to 15 on header

  UPDATE ABC001 item 1 with value of 10, then add -5 to 20 on header

  [10 - 15 = -5]

  you should now have 15 as header value on item ABC001

  [10 from item 1 and 5 from item 2]

  hope this helps