Running Calculation based on set Criteria

[JamesMF82]

Hi,

I have a query I am trying to build currently which creates a running total based on set criteria. I essentially have three columns (All times are in minutes). The visit ID, the duration of a visit and the time difference between the current and previous visit. e.g.

ID Duration Difference
1 30 0
2 45 10
3 15 20
4 60 10
5 15 10

What I would like to achieve is the following:

ID Duration Difference running total
1 30 0 30
2 45 10 85
3 15 20 15
4 60 10 85
5 15 10 110


So a line will have the calculation of the current time + the difference between the current and previous visit if the difference is below 20.
I have the following:

Public Function Cont20(MyVal As Long, MyDif As Long) As Long

Static OldValue As Long
Dim NewValue As Long

If MyDif >= 20 Then
   NewValue = MyVal
   OldValue = 0
Else
   NewValue = MyVal + OldValue
   OldValue = NewValue
End If

Cont20 = NewValue

End Function

Which I put as Cont20([Duration],[Difference])

the problem seems to be on the return aspect, its not calculating correctly.

I had the formula in Excel which worked great, but this is proving troublesome. As a note the Excel formula was such that it would add the previous running total and then add the current duration and current diff. e.g.

In cell A7 the formula would be A6+B7+C7 and so on.

Any help would be awesome!

 

[plog]

You don't really provide an understandable explanation of how you want your running total calculated. Explain in words how you determined each line of your desired results.

 

[pr2-eugin]

You are looking at a complicated Running Sum.. Could you not filter out the ones that are below 20?

One thread to start with : http://www.access-programmers.co.uk/forums/showthread.php?t=158467

 

[plog]

Disregard my first post, I got it. Here's the VBA function:

Public Function get_RunningTotal(i)
' returns running total of Duration field in YourTableNameHere ordered by ID (i)
 
Dim ret As Double
    ' will contain return value
 
Dim FirstID As Integer
    ' will hold first ID that has a Difference of 20 or more
 
ret = 0
FirstID = DMin("[ID]", "YourTableNameHere")
    ' sets default values
 
If (DCount("[ID]", "YourTableNameHere", "[ID]<=" & i & " AND [Difference]>=20") > 0) Then FirstID = DMax("[ID]", "YourTableNameHere", "[ID]<=" & i & " AND [Difference]>=20")
    ' if table has ID with Difference greater than 20 with an ID lower than the current ID (i), will update FirstID
 
ret = DSum("[Duration] + [Difference]", "YourTableNameHere", "[ID]>=" & FirstID & " AND [ID]<=" & i)
 
ret = ret - DLookup("[Difference]", "YourTableNameHere", "[ID]=" & FirstID)
    ' calculates running total from FirstID to current ID (i) and subtracts Difference value of FirstID
 
get_RunningTotal = ret
 
End Function

Replace instance of "YourTableNameHere" with the name of your table

Then to call it in a query use this:

RunningTotal: get_RunningTotal([ID])

 

[JamesMF82]

Hi,

Sorry about that. Ok so with what I would like to see in my results based on the table is (excuse the periods they are purely for spacing):

ID..Duration....Difference.Running total (RT).........Workings for the RT
1......30..............0..............30............As diff is under 20 duration + dif
2......45..............10.............85............diff is <20, last RT + dur +dif
3......15..............20.............15............diff is >=20, so dur +dif
4......60..............10.............85............diff is <20, last RT + dur +dif
5......15..............10............110............diff is <20, last RT + dur +dif

So if the difference is less than 20, the calculation works on the previous running total + the current duration + the difference. If it is over then it is just the duration.

 

[JamesMF82]

@plog

I will have a play with this and let you know, thanks

 

[JamesMF82]

Thanks Plog!