Running Total with Limit? (1 Viewer)

[ErinL]

Hello everyone!

I am wondering if it's possible to have a running total either in a query or using the Running Sum function on a text box on a report that will reset after a specific value.

Here is what I would like to have happen:

The RunningTotalCube field to reset when it has reach 2.3 or whatever number comes closest to that number.

Date Time Item Cube RunningTotalCube
4-2-15 12:05 15615 0.5 0.5
4-2-15 12:06 15918 0.8 1.3
4-2-15 12:10 98563 0.5 1.8
4-2-15 12:12 45268 0.4 2.2
4-2-15 12:15 25854 0.9 0.9 {reset}
4-2-15 12:17 75136 0.5 1.4

Is this possible either in the query or the report/in Access or in VBA?

Thank you in advance!

 

[plog]

It's possible with VBA, but not in a straight SQL.

First, you need to consolidate and better name your data. Both "Date" and "Time" are reserved words in Access and shouldn't be used for field names. It makes coding harder. You should prefix the field name with what that Date/Time represents (e.g. CubeTime). Also, you shouldn't have 2 fields for that data. The field type of both of those is "Date/Time" which means you can get both the date and the time values into 1 field. That's how this data should be used.

For your issue, you will need to write a VBA function, pass it the Date/Time of a record, it would use a Recordset to loop through all the data in the table up to that point and keep a running total of what the RunningTotal should be and it would return the value it has when it reaches the data of the record for the date/time you passed it.

 

[ErinL]

Thanks for your response. My field names are actually Pick Date and Pick Time. I shortened them for the post just to save a few keystrokes and space. The information is in two separate fields because this information is imported from a .csv file and it is two separate fields in that file so I left it as it was for simplicity in importing.

I'm afraid I am very much a beginner when it comes to writing VBA code so, unfortunately, the last paragraph of your response sounded good to me but I have no idea how to do that.

I did do some research on loops and tried to give it a shot but what I have doesn't work. Here is what I came up with on my own:

Sub RunningTotal()

Dim Runtotal As Integer
Dim Cube As Integer
Dim TotalCube As Integer

Runtotal = 0
TotalCube = Cube + Runtotal

Do While TotalCube <= 1.875
TotalCube = Cube + Runtotal
If Cube + Runtotal > 1.875 Then
Exit Do
End If

Loop
End Sub

Could you help me a little more with the writing of the loop as you suggested?

 

[plog]

This is the function you need:

Function get_LimitedRunningTotal(in_Time As Date, in_Limit As Double, in_Table As String, in_TotalField As String, in_OrderField As String) As Double
    ' gets running total up to specific value (in_Time) and resets running total whenever it goes over in_Limit
    
ret = 0          ' return value, by default will be 0
    
str_SQL = "SELECT " & in_TotalField & " FROM " & in_Table & " WHERE " & in_OrderField & "<=#" & in_Time & "# ORDER BY " & in_OrderField
    ' SQL to use for calculating running total

Set rs_Cubes = CurrentDb.OpenRecordset(str_SQL)
    ' sets SQL to recordset so can loop through it
    
If rs_Cubes.Recordcount <> 0 Then
    ' if recordset has data, will calculate running total
    rs_Cubes.MoveFirst
    While Not rs_Cubes.EOF
        If ret > in_Limit Then ret = 0
    ' if recordset over limit, resets it to 0
        ret = ret + rs_Cubes(0)
    ' adds current value in recordset to total
        rs_Cubes.MoveNext
        Wend
    End If

rs_Cubes.Close
Set rs_Cubes = Nothing
    ' closes out recordset
    
get_LimitedRunningTotal = ret
  
    
End Function

You pass it the date/time of the current record (in_Time), the limit at which the running total should be reset (in_Limit), the name of the table to use (in_Table), the field in that table to add up (in_TotalField) and the field in that table that contains the Date/Time value to order your data by (in_OrderField) and it will return the running total up to that record applyin the limiter.

To use it in a query, it would look something like this:

RuningTotal: get_LimitedRunningTotal([CubeTime],25,"YourTableNameHere","Cube","CubeTime")

This works when your date/time data is in a single field and not two different fields.