I need to create a query that keeps a day to day running sum of [RunHours] but starts over at the beginning of each month. Any help would be greatly appreciated. thanks.
For example:
Date RunHours MonthToDate
12/8/06 24 145
12/6/06 20 121
12/5/06 19 101
12/4/06 17 82
12/3/06 20 65
12/2/06 22 45
12/1/06 23 23
11/30/06 22 115
11/29/06 21 93
11/28/06 16 72
11/27/06 17 56
11/26/06 19 39
11/25/06 20 20
Follow these steps in sequence:
1) Create a SELECT Query with these Field designations
[Date] - Ascending
[RunHours] - Ascending
'a Calculated Field
MonthToDate:fCalculateRunningSum([Date], [RunHours])
2) Reset Public Variables prior to Query execution:
- 'Must reinitialize to 0 before Query runs because of
-
'Variables being declared Publically
-
lngNov = 0
-
lngDec = 0
3) The Date and Run Hours are passed to the Public Function which
calculates a Running Sum for each Month. Only a subset of the code
for November and December has been provided, the remainder should
be self-explanatory. If you need further assistance, please let me know.
I was rather pressed for time when I figured out the logic.
- Public Function fCalculateRunningSum(MyDate As Date, intRunHours As Long)
-
Select Case Month(MyDate)
-
Case 1
-
Case 2
-
Case 3
-
Case 4
-
Case 5
-
Case 6
-
Case 7
-
Case 8
-
Case 9
-
Case 10
-
Case 11
-
lngNov = lngNov + intRunHours
-
fCalculateRunningSum = lngNov
-
Case 12
-
lngDec = lngDec + intRunHours
-
fCalculateRunningSum = lngDec
-
Case Else
-
End Select
-
End Function