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

Running Sum in a Query (Module Help)

P: 46
Hello,

I have searched for help on this and have not been successful in finding an answer to my particular problem.

I am trying to compute a running sum of sales in a query.

Here is the query output as of now:



The "gross" column is the amount of sales in that particular month. I would like the "progress" field to be the running sum.

As you can see, I've made some progress (using the Microsoft Northwind database sample and this link: http://support.microsoft.com/kb/205183

However, notice how the last cell (for March) goes back to $152,000 instead of adding to the prior total.

Also, I would like the module to maintain the current total for the rest of the year. That is, for the months of April through September, I'd like to current progress to appear (about $185mm).

Here is the module code I am using:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Function bbrunsum(production_month As Date, gross_amount As Currency) As Long
  5.     Static pro_month As Date
  6.     Static gross As Currency
  7.  
  8.     If pro_month = production_month Then
  9.         pro_month = production_month
  10.         gross = gross_amount
  11.     Else
  12.         gross = gross + gross_amount
  13.     End If
  14.     bbrunsum = gross
  15. End Function
Any help is very much appreciated.

Thanks,

Brian
Jan 5 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 374
Hey Brian,

What is it that you are passing to the variable (Pro_Month), is it the date that is in the table labeled Pro_Month?

If so, what the original document was referring too is, that it is an numeric value that is passed that gives the current block or group a unique value that is used to do the running sum on. If you want to define it per ficial year, then you would pass it a same numeric value for the whole ficial year. i.e. (Format([pro_month]+Month(3),"yyyy"). When your new ficial years starts.

Make changes to the code that are in BOLD and that should take care of having the running total displayed.

Option Compare Database
Option Explicit

Function bbrunsum(production_month As Long, gross_amount As Currency) As Long
Static pro_month As Long
Static gross As Currency

If pro_month = production_month Then
pro_month = production_month
gross = gross_amount
Else
gross = gross + NZ(gross_amount,0)
End If
bbrunsum = gross
End Function



Example:

FY2008 is something that is calculated on the Query, I'm assuming. If you convert that to a number and then pass that to the routine, and change the Date definition back to Long, then that should take care of how the items are grouped as well as when it knows to reset the running sum for the following ficial year.

If the item in March keeps reseting, then you need to look at the data for that month and see if there is something that may have an incorrect year or something that is triggering the reset of the variable that is storing the sum total that is currently running.

Hope that helps,

Joe P.
Jan 5 '08 #2

Post your reply

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