473,325 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Running sums by month

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
6 2440
ADezii
8,834 Expert 8TB
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
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
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
8,834 Expert 8TB
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
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
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

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

Similar topics

5
by: Stephen Miller | last post by:
Hi, I am trying to add a staggered running total and average to a query returning quarterly CPI data. I need to add 4 quarterly data points together to calculate a moving 12-month sum (YrCPI),...
2
by: Duane123 | last post by:
Hello, any help is appreciated. Here is what I’m trying. I want a month to date total of bookings based on a fiscal month. I can isolate my records for the month and sum up the days so I wind up...
2
by: Bas de Laat | last post by:
I'm trying to fix a running (12 month rolling) sum on a large table (over 50.000) record. I found already several good advices. First I Tried it with an Subquery But for large tables it takes to...
6
by: KashMarsh | last post by:
Trying to show running totals on a report, except it needs to show one total amount and values being subtracted from it. For example, the report shows a Total Inventory amount (TotInvAmt). And...
9
by: rinmanb70 | last post by:
I have a table of transactions, some with past dates, some dated the current date, and some dated in the near future. On a report, I'm looking for a way to get four different sums using the...
1
by: Tom Brown | last post by:
I have a report I created which pulls all data by month by category and gives me running totals month by month. My problem is I need the ending balance running total to move down to the next line...
9
by: JJM0926 | last post by:
I'm trying to create a running totals query in access 97. I have followed the directions on how to do it from Microsofts website article id 138911. I took their code they had and replaced it with...
6
by: Stuart Shay | last post by:
Hello All: I have a array which contains the totals for each month and from this array I want to get a running total for each month decimal month = new decimal; month = 254; (Jan) month =...
5
by: BillShaw | last post by:
Using Access 2003 I am trying to construct a query to provide data for a stacked column graph to show cumulative sales figures month on month individually by salesman. Eg. a column will represent a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.