• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

Sql Update Query

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
mlcktmguy
Asked:
mlcktmguy
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
mlcktmguyAuthor Commented:
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
 
PatHartmanCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Gustav BrockCIOCommented:
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
 
ButlerTechnologyCommented:
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
 
mlcktmguyAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now