Grouped running sum

[mndiscer]

Hello I have an issue where I think the solution is simple.

I have a table with two columns
Type=Text
Ert=Number

I want to create a query that has a 3rd column that does a rolling sum.
Example
Black 10 10
Black 5 15
Green 2 2
Green 1 3

I've tried the fncRunSum function with no luck, I get "#error" in the calc column.

Any help would be great.

Ben

 

[dhookom]

You would need another column that identifies an order within the Type.

Why:
[tt]
Type Ert RunSum
Black 10 10
Black 5 15
Green 2 2
Green 1 3
[/tt]
and not
[tt]
Type Ert RunSum
Black 5 5
Black 10 15
Green 1 1
Green 2 3
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[mndiscer]

Thanks for the lead, but what I did was I changed the ingCatID to String and IngID to String.
So it reads:

Function fncRunSum(lngCatID As String, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As String
Static lngAmt As Long

If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If

'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function

Ben