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
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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

Comment Utility
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 34

Assisted Solution

PatHartman earned 75 total points
Comment Utility
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.
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 175 total points
Comment Utility
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
Comment Utility
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

Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now