Sql Update Query

Posted on 2014-01-04
Last Modified: 2014-01-05
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.
Question by:mlcktmguy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39756931
you don't need to  store calculated values, you can always derived them using a query.

try this query

select Account,Sum(Principal),Sum(Interest),sum(Penalties)
from NameOf Table
group by Account

Author Comment

ID: 39756965
I agree with you but this particular clients computers and network are terribly slow.  I'm pretty sure that the reason they are stored in the header is to save time in showing those totals.  For an on screen recap of accounts or a report with a recap of accounts the numbers can be taken from the headers rather than running queries to create the result.

Theoretically each transaction that would effect the balances in the headers take place, the logic should update the header.  This is the part that has holes in it now.

Is there query syntax that could handle this updating?
LVL 37

Assisted Solution

PatHartman earned 75 total points
ID: 39757005
Access requires that all parts of a query be updateable for any part to be updateable.  Since a totals query is not updateable, you can't use a simple update query.  There are two ways to do this and I would choose the method based on whether this is a one time effort or you intend to incorporate into a frequent process.

As a one-time event, I would create a totals query to sum the detail and convert that to a make-to save the summed amount.  Then I would create an update query that joins the header table to the temp table and updates the summary data.

I don't like temp tables since they bloat the database so if I were going to incorporate this into a weekly or monthly process, I would use a VBA code loop that reads the two recordsets sorted into primary key sequence and updates the header with the data from the totals query.
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 175 total points
ID: 39757093
I guess you have a form with a subform. If so, you don't need an update query, just use the AfterUpdate and AfterInsert events of the subform to call this code:
Private Sub UpdateHeader()

Dim rst As DAO.Recordset
Dim curPrincipal As Currency
Dim curInterest As Currency
Dim curPenalties As Currency

Set rst = Me.RecordsetClone
' Build sums.
While Not rst.EOF
    curPrincipal = curPrincipal + rst!Principal
    curInterest  = curInterest + rst!Interest
    curPenalties = curPenalties + rst!Penalties
' Fill sums in header.
Me.Parent!Principal = curPrincipal 
Me.Parent!Interest = curInterest
Me.Parent!Penalties = curPenalties
' Save header.
Me.Parent.Dirty = False

Set rst = Nothing

End Sub

Open in new window


Accepted Solution

ButlerTechnology earned 250 total points
ID: 39757517
I have a similar data structure with Invoice Header and Invoice Item.  I created a routine that is executed each time that an  Invoice Item is updated which updates the Invoice Header.   The nice part is that you can create a routine that loops trough all of your header item and call the routine to update all records.  I cheat sometimes and just built a simple for loop -- assuming that the ID is an integer.

Public Sub UpdateInvoiceAmount(InvoiceID As Integer)
Dim InvoiceAmount As Currency

  InvoiceAmount = Nz(DSum("LineTotal", "InvoiceDetail", "InvoiceID = " & InvoiceID), 0)
  runSQL "Update InvoiceHeader set InvoiceAmount = " & InvoiceAmount & " where InvoiceID = " & InvoiceID
End Sub

Open in new window

Here's the code for the runSQL function as I tend to like writing real SQL in my applications.
Public Sub runSQL(SQL As String)
On Error GoTo Err_Catch

  DoCmd.SetWarnings False
  DoCmd.runSQL SQL
  DoCmd.SetWarnings True

  Exit Sub
  MsgBox Err.Number & ": " & Err.Description
  Resume Err_Resume
End Sub

Open in new window


Author Closing Comment

ID: 39757936
I  awarded the original 250 to Butler Technologies since that is the approach I used to re-synch my headers.

I up the original points to 500 and split the additional 250 among the other answers which were very informative and willprobably be used to resolve keep the out of synch condition from occurring in an ongoing basis.

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question