I inherited an app that has some integrity holes in it. While I am trying to work thru and resolve the issues I want to write a routine that will ensure that the detail records are in synch with the header record.
The header record table contains a summary of the detail. This is a loan system so the header record among other fields contains Account and Principal, Interest and Penalties that are a summary of all the detail records for an account.
The header record (tblAccountHdr) for an account might contain:
Account Principal Interest Penalties
111111 1300 200 50
The detail records(tblAccountYears) would possibly contain:
Account Year Principal Interest Penalties
111111 2009 1000 125 10
111111 2010 0200 050 15
111111 2011 0100 025 25
In reality the header totals do not match the detail totals in some cases. In the application the header should be updated anytime something changes in the detail but that is where the current hole in the logic is.
I would like to write an update query that could be called to update contents of an account header to be accurately reflect the sum of the respective amount in the detail.
I have written many queries that set the value of a field in a table based on the contents of a field in another table. However I have never written one that has to update a field in one table with the sum of multiple records from another table.
What would the query look like to do this for an particular account, or all accounts.