Running Sum in a Query (Module Help)

[bbatson]

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:

Option Compare Database
Option Explicit
 
Function bbrunsum(production_month As Date, gross_amount As Currency) As Long
    Static pro_month As Date
    Static gross As Currency
 
    If pro_month = production_month Then
        pro_month = production_month
        gross = gross_amount
    Else
        gross = gross + gross_amount
    End If
    bbrunsum = gross
End Function

Any help is very much appreciated.

Thanks,

Brian

[PianoMan64]

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(produc tion_month As Long, gross_amount As Currency) As Long
Static pro_month As Long
Static gross As Currency

If pro_month = production_mont h Then
pro_month = production_mont h
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.