Adding consecutive values to get a running total

[JamesMF82]

Hi,

I have a quick (hopefully) query about adding up consecutive values to get a running total.
Currently I have the following

ID Value
1 0
2 5
3 2
4 0
5 1
6 30
7 2

etc...

I am looking for a way to get a running total up to the point of a 0 then restarting. e.g.

ID Value Running total
1 0 0
2 5 5
3 2 7
4 0 0
5 1 1
6 30 31
7 2 33


Any help would be much appreciated.

Thanks,

 

[CJ_London]

try

 SELECT *, (SELECT Sum(myValue) FROM myTable as Tmp WHERE ID<=myTable.ID) as RunningTotal FROM myTable

You'll need to change field and table names to suit

Sorry, just read the bit about reseting so this needs more work

 

[plog]

Have you searched this forum for running total query? Its a frequently asked question and you should find a lot of resources. You essentially use a DSUM (http://www.techonthenet.com/access/functions/domain/dsum.php) to sum up the value field for all records up to a point.

Use one of those solutions and change the criteria argument to include logic to start at the last '0' record in the series.

 

[CJ_London]

Try this instead

SELECT *, IIf([myValue]=0,0,(SELECT Sum(myValue) FROM myTable as Tmp WHERE ID BETWEEN (SELECT max(ID) FROM myTable as tmp2 WHERE ID<myTable.ID AND myValue=0) AND myTable.ID)) AS RunningTotal 
FROM myTable

 

[JamesMF82]

^Thanks for this!

@Plog - Quick question, I did search better after and came up with a DSUM query, however when it seemed to ignore the criteria of the ID. Just for my own peace of mind, can you see why?

Running Total: DSum("[myvalue]","myTable","ID<=ID")

 

[JamesMF82]

Running Total: DSum("[myvalue]","[myTable]","ID<="&[ID]) --> seems to work.

Now if only I hadn't run it on 150,000 lines first of all!

 

[JamesMF82]

@CJ

Hi,

I was hoping you could advise me on one last part. I have tried putting my information into your selection, but I am getting a few errors. The values I have against the spacers are:
myvalue = DifCri
myTable = 03_Difference_Criteria
ID = VisitID

The formula I have is the following:

SELECT *,
IIf([DifCri]=0,0,(SELECT Sum(DifCri) FROM 03_Difference_Criteria as Tmp WHERE VisitID BETWEEN (SELECT max(VisitID) FROM 03_Difference_Criteria as tmp2 WHERE VisitID<03_Difference_Criteria.VisitID AND DifCri=0) AND 03_Difference_Criteria.VisitID)) AS RunningTotal
FROM 03_Difference_Criteria

However I am getting a symtax error on frm if IIF statement.

Sorry to ask again.

Regards

James

 

[spikepl]

For fun, try this:

Public Function RunningSum(MyVal as Long) As Long

Static OldValue as Long
Private NewValue As Long

If MyVal=0 Then
   NewValue=0
Else
   NewValue=MyVal+OldValue
End IF

OldValue=NewValue

RunningSum=NewValue

End Function

and 'Value is a lousy name for a coilumn

Select Id, [Value], RunSum: RunningSum([Value])

 

[CJ_London]

This is intended to run as a query but it looks like you want it on a form? so it would need to be the recordsource for the form (or of a subform) or you could use it as a rowsource for a listbox.

I can't actually see anything wrong with the syntax - I created a table called myTable with the fields as per the post and it ran OK, but I have read somewhere that between only works for dates, it worked for me so not sure what to believe!

On the basis that between only works for date then here is a revised code (not tested):

SELECT *, 
IIf([DifCri]=0,0,(SELECT Sum(DifCri) FROM 03_Difference_Criteria as Tmp WHERE VisitID >= (SELECT max(VisitID) FROM 03_Difference_Criteria as tmp2 WHERE VisitID<03_Difference_Criteria.VisitID AND DifCri=0) AND VisitID <=03_Difference_Criteria.VisitID)) AS RunningTotal FROM 03_Difference_Criteria

 

[JamesMF82]

Hi,

It's going to be run in as a query and not a form. I will have a play around with it.

Thanks all

 

[JamesMF82]

@spikepl

Tried the function, but alas no joy. was getting the error

Compile error. in expression 'RunningSum([DifCri]'.

DifCri being the column I am trying to run the calculation on.

I added a new module under the database name in VBA and put in your code.

***Update***
Adding the table name to the table field in the Query returns Syntax error in string in query expression '[03_Difference_Criteria].[RunningSum("[DifCri]")]

 

[plog]

This should provide what you want. Paste this code into a module:

Function getRunningSum(i)
    ' gets running sum of an ID value (i) starting from prior value that is 0
    
Dim ret, LastZero As Integer          ' ret is return value, LastZero is last ID with Value=0
  
ret = LastZero = 0
    ' by default both start at 0
    
    
If IsNull(DMax("[ID]", "TableNameHere", "[Value]=0 AND [ID]<=" & i)) = False Then LastZero = DMax("[ID]", "TableNameHere", "[Value]=0 AND [ID]<=" & i)
    ' gets last 0 value record prior to i
    
ret = DSum("[Value]", "TableNameHere", "[ID]<=" & i & " AND [ID]>=" & LastZero)
    ' gets running total from ID=LastZero to ID=i
    
getRunningSum = ret
End Function

Replace all instances of 'TableNameHere' with the name of your table. Then to use it in a query you would do this:

RunningSum: getRunningSum([ID])

 

[spikepl]

Private NewValue As Long ->
Dim NewValue As Long

Note the function relies on your data being in sequence, so the query must include an Order By