By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,161 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,161 IT Pros & Developers. It's quick & easy.

Running sums by month

P: 5
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
Jan 3 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,669
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:
Expand|Select|Wrap|Line Numbers
  1. 'Must reinitialize to 0 before Query runs because of
  2. 'Variables being declared Publically
  3. lngNov = 0
  4. 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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateRunningSum(MyDate As Date, intRunHours As Long)
  2. Select Case Month(MyDate)
  3.   Case 1
  4.   Case 2
  5.   Case 3
  6.   Case 4
  7.   Case 5
  8.   Case 6
  9.   Case 7
  10.   Case 8
  11.   Case 9
  12.   Case 10
  13.   Case 11
  14.       lngNov = lngNov + intRunHours
  15.       fCalculateRunningSum = lngNov
  16.   Case 12
  17.       lngDec = lngDec + intRunHours
  18.       fCalculateRunningSum = lngDec
  19.   Case Else
  20. End Select
  21. End Function
Jan 3 '07 #2

P: 5
thanks for your help.
I followed those steps, but when I added the new module and declared the public variables, all the other queries using the [date] field in expressions, give a compile error. When I remove the new code all the old queries work again.
Can this be done with a private sub?
Jan 3 '07 #3

P: 5
I figured out part of my problem, I was not declaring these initial variables correctly.

How/where should I set these initial values?

'Must reinitialize to 0 before Query runs because of
'Variables being declared Publically
lngNov = 0
lngDec = 0
Jan 3 '07 #4

ADezii
Expert 5K+
P: 8,669
I figured out part of my problem, I was not declaring these initial variables correctly.

How/where should I set these initial values?

'Must reinitialize to 0 before Query runs because of
'Variables being declared Publically
lngNov = 0
lngDec = 0
These values should be set immediately prior to running the Query via VBA,
Macro, etc.
Jan 4 '07 #5

P: 5
Thanks for your help, it gave me a good start.
If anyone else is interested, I ended up finding a piece of code at
http://support.microsoft.com/kb/205183
and modified it to group by month. It shortens the code significantly.

the expression in the query remains as:

CalculatedFieldName: fncRunSum([Date],[RunHours])
Jan 5 '07 #6

P: 5
oops, here's the whole thread,

Thanks for your help, it gave me a good start.
If anyone else is interested, I ended up finding a piece of code at
http://support.microsoft.com/kb/205183
and modified it to group by month. It shortens the code significantly.

the expression in the query remains as:

CalculatedFieldName: fncRunSum([Date],[RunHours])
Jan 5 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.