INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Running Sum In Queries

How to get a Running Sum in Queries by TheAceMan1
Posted: 3 Jul 04 (Edited 18 Oct 13)

How's Everyone . . . . .

See FAQ702-5248: How to get a Running Sum In Forms for Running Sum in Forms.

I've been asked and researched a great many requests for a running sum in queries. Research has come up with a DSum method (which is slow) and other methods which are dependant on a key field, specifically sorted in ascending order (this is a severe limitation). Finally ran into a routine that is faster than DSum and independant of sorting. I don't know who to give the credit too for the foundation of the base routine, but you can find it in Microsofts Knowledge Base http://support.microsoft.com/default.aspx?scid=kb;.... What I present here is a modification as the origional was designed for forms.

Advantages of the routine:
1) Faster than DSum.
2) Independance of Sorting.

The routine is dependant on referencing Microsoft DAO 3.6 Object Library. To insure this, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Now . . . . create a new module in the module window. Call it modRunSum or whatever you like. Add the following to the declarations section:
Option Explicit

Next . . . . add the following code to the same module (this is the global running sum routine):

CODE

Public Function RecRunSum(qryName As String, idName As String, idValue, sumField As String)
   'qryName  - Name of the query calling this function
   'idName   - UniqueFieldName ... usually the primarykey
   'idValue  - The value of UniqueFieldName in the line above.
   'sumField - The name of the field to runsum
   Dim db As DAO.Database, rst As DAO.Recordset, subSum
   
   Set db = CurrentDb()
   Set rst = db.OpenRecordset(qryName, dbOpenDynaset)
      
   'Find the current record via proper syntax for Data Type.
   Select Case rst.Fields(idName).Type
      Case dbLong, dbInteger, dbCurrency, _
           dbSingle, dbDouble, dbByte 'Numeric
         rst.FindFirst "[" & idName & "] = " & idValue
      Case dbText 'Text
         rst.FindFirst "[" & idName & "] = '" & idValue & "'"
      Case dbDate 'Date
         rst.FindFirst "[" & idName & "] = #" & idValue & "#"
      Case Else 'Unknown data type returns Null
         rst.MovePrevious 'Move to BOF
   End Select
   
   'Running Sum (subTotal) for each record occurs here.
   Do Until rst.BOF
      subSum = subSum + Nz(rst(sumField), 0)
      rst.MovePrevious
   Loop
   
   RecRunSum = subSum
   
   Set rst = Nothing
   Set db = Nothing
      
End Function 
Now . . . . . add a custom field in the query (on the field line in a blank field) using one of the following lines: (purple only), depending on the data type of the field desired:

CODE

Example Data
------------
QueryName  = qryInv
UniqueID   = InvID
FieldToSum = Cost

Byte ..... RunSum: CByte(RecRunSum("qryInv","InvID",[InvID],"Cost"))
Currency . RunSum: CCur(RecRunSum("qryInv","InvID",[InvID],"Cost"))
Double ... RunSum: CDbl(RecRunSum("qryInv","InvID",[InvID],"Cost"))
Integer .. RunSum: CInt(RecRunSum("qryInv","InvID",[InvID],"Cost"))
Long ..... RunSum: CLng(RecRunSum("qryInv","InvID",[InvID],"Cost"))
Single ... RunSum: CSng(RecRunSum("qryInv","InvID",[InvID],"Cost")) 
Thats it! . . . . give it whirl and let me know if anyone has any problems.

Any input on this schema, good or bad, is most certainly welcome. I also invite the ADO Guru's to incite an ADO version.

Cheers All!

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close