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 Basehttp://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):
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
'Running Sum (subTotal) for each record occurs here.
Do Until rst.BOF
subSum = subSum + Nz(rst(sumField), 0)
RecRunSum = subSum
Set rst = Nothing
Set db = Nothing
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:
QueryName = qryInv
UniqueID = InvID
FieldToSum = CostByte ..... 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.