Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to get a Running Sum in Queries

Running Sum In Queries

How to get a Running Sum in Queries

by  TheAceMan1  Posted    (Edited  )
How's Everyone . . . . .

See faq702-5248 for [blue]Running Sum in Forms[/blue].

I've been asked and researched a great many requests for a [blue]running sum in queries[/blue]. 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 [purple]Microsofts Knowledge Base[/purple] http://support.microsoft.com/default.aspx?scid=kb;en-us;210338. 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 [blue]Microsoft DAO 3.6 Object Library[/blue]. To insure this, click [blue]References[/blue] on the [blue]Tools[/blue] menu in the Visual Basic Editor, and make sure that the [blue]Microsoft DAO 3.6 Object Library[/blue] check box is selected.

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

Next . . . . add the following code to the same module ([purple]this is the global running sum routine[/purple]):
Code: 
[blue]Public Function RecRunSum(qryName As String, idName As String, idValue, sumField As String)
   [green]'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[/green]
   Dim db As DAO.Database, rst As DAO.Recordset, subSum
   
   Set db = CurrentDb()
   Set rst = db.OpenRecordset(qryName, dbOpenDynaset)
      
   [green]'Find the current record via proper syntax for Data Type.[/green]
   Select Case rst.Fields(idName).Type
      Case dbLong, dbInteger, dbCurrency, _
           dbSingle, dbDouble, dbByte [green]'Numeric[/green]
         rst.FindFirst "[" & idName & "] = " & idValue
      Case dbText [green]'Text[/green]
         rst.FindFirst "[" & idName & "] = '" & idValue & "'"
      Case dbDate [green]'Date[/green]
         rst.FindFirst "[" & idName & "] = #" & idValue & "#"
      Case Else [green]'Unknown data type returns Null[/green]
         rst.MovePrevious 'Move to BOF
   End Select
   
   [green]'Running Sum (subTotal) for each record occurs here.[/green]
   Do Until rst.BOF
      subSum = subSum + Nz(rst(sumField), 0)
      rst.MovePrevious
   Loop
   
   RecRunSum = subSum
   
   Set rst = Nothing
   Set db = Nothing
      
End Function[/blue]
Now . . . . . add a custom field in the query (on the field line in a blank field) using one of the following lines: ([purple]purple only[/purple]), depending on the data type of the field desired:
Code: 
[tt]Example Data
------------
QueryName  = qryInv
UniqueID   = InvID
FieldToSum = Cost[/tt]

[blue]Byte ..... [purple][b]RunSum: CByte(RecRunSum("qryInv","InvID",[InvID],"Cost"))[/b][/purple]
Currency . [purple][b]RunSum: CCur(RecRunSum("qryInv","InvID",[InvID],"Cost"))[/b][/purple]
Double ... [purple][b]RunSum: CDbl(RecRunSum("qryInv","InvID",[InvID],"Cost"))[/b][/purple]
Integer .. [purple][b]RunSum: CInt(RecRunSum("qryInv","InvID",[InvID],"Cost"))[/b][/purple]
Long ..... [purple][b]RunSum: CLng(RecRunSum("qryInv","InvID",[InvID],"Cost"))[/b][/purple]
Single ... [purple][b]RunSum: CSng(RecRunSum("qryInv","InvID",[InvID],"Cost"))[/b][/purple][/blue]
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 [blue]ADO Guru's[/blue] to incite an ADO version.

Cheers All!
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top