Solved

Sql Update Query

Posted on 2014-01-04
6
435 Views
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.
0
Comment
Question by:mlcktmguy
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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
0
 
LVL 1

Author Comment

by:mlcktmguy
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?
0
 
LVL 34

Assisted Solution

by:PatHartman
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.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 49

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
Wend
' 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

/gustav
0
 
LVL 6

Accepted Solution

by:
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

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

Open in new window

0
 
LVL 1

Author Closing Comment

by:mlcktmguy
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.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to not remove leading zeros 9 38
TRIM a textbox value MS Access 10 23
bind Combobox 4 29
Getting Run-Time Error 13 - Type Mismatch 3 26
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 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

16 Experts available now in Live!

Get 1:1 Help Now