Running Sum

[Vina]

I have a function to calculate the running sum but when I
close the query or re-run it it remembers the last sum
and end up subtracting the wrong amt. How can I add
something in this function that will reset the Running
total when the query is closed or everytime the query
gets run again it will be a fresh calculation. The only
way I can get a fresh calculation is when I close the
database.

Function fncRunSum(lngCatID As Long, lngOhnd As Long,
lngUnits As Long) As Long
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
lngID = lngCatID
lngAmt = lngOhnd - lngUnits
Else
lngAmt = lngAmt - lngUnits
End If
fncRunSum = lngAmt

End Function

 

[Gary Walter]

I have a function to calculate the running sum but when I
close the query or re-run it it remembers the last sum
and end up subtracting the wrong amt. How can I add
something in this function that will reset the Running
total when the query is closed or everytime the query
gets run again it will be a fresh calculation. The only
way I can get a fresh calculation is when I close the
database.

Function fncRunSum(lngCatID As Long, lngOhnd As Long,
lngUnits As Long) As Long
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
lngID = lngCatID
lngAmt = lngOhnd - lngUnits
Else
lngAmt = lngAmt - lngUnits
End If
fncRunSum = lngAmt

End Function

Hi Vina,

The only thing I can think to do
would be to add a "Reset" parameter,
and always run a query with this parameter
set before you run your running total query.

Function fncRunSum(lngCatID As Long, lngOhnd As Long,
lngUnits As Long, pReset As Boolean) As Long
Static lngID As Long
Static lngAmt As Long

If pReset = True Then
lngID=0
lngAmt=0
fncRunSum=-1
Exit Function
End If

If lngID <> lngCatID Then
lngID = lngCatID
lngAmt = lngOhnd - lngUnits
Else
lngAmt = lngAmt - lngUnits
End If
fncRunSum = lngAmt

End Function

So qryReInit might look like

SELECT fncRunSum(0,0,0,-1)
FROM sometable;

which you would always need to run
before your running sum query.

And then in your running sum query,
new final parameter for your function
would be 0.

 

[Gary Walter]

I have a function to calculate the running sum but when I
close the query or re-run it it remembers the last sum
and end up subtracting the wrong amt. How can I add
something in this function that will reset the Running
total when the query is closed or everytime the query
gets run again it will be a fresh calculation. The only
way I can get a fresh calculation is when I close the
database.

Function fncRunSum(lngCatID As Long, lngOhnd As Long,
lngUnits As Long) As Long
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
lngID = lngCatID
lngAmt = lngOhnd - lngUnits
Else
lngAmt = lngAmt - lngUnits
End If
fncRunSum = lngAmt

End Function

Hi Vina,

The only thing I can think to do
would be to add a "Reset" parameter,
and always run a query with this parameter
set before you run your running total query.

Function fncRunSum(lngCatID As Long, lngOhnd As Long,
lngUnits As Long, pReset As Boolean) As Long
Static lngID As Long
Static lngAmt As Long

If pReset = True Then
lngID=0
lngAmt=0
fncRunSum=-1
Exit Function
End If

If lngID <> lngCatID Then
lngID = lngCatID
lngAmt = lngOhnd - lngUnits
Else
lngAmt = lngAmt - lngUnits
End If
fncRunSum = lngAmt

End Function

So qryReInit might look like

SELECT fncRunSum(0,0,0,-1)
FROM sometable;

which you would always need to run
before your running sum query.

And then in your running sum query,
new final parameter for your function
would be 0.


Please respond back if I have misunderstood
or was not clear.

Good luck,

Gary Walter