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