[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Sql Update Query

Posted on 2014-01-04
Medium Priority
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 39

Assisted Solution

PatHartman earned 300 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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 700 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 1000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

649 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