Cumulative Total

[SBM]

Can any one help me please !

I have a table with two columns....ProdCode and FCast (sorted descending). I want to run a query / Create a table which will show ProdCode, FCast and Cumulative Fcast.

For example

ProdCode Fcast Cumulative
C 10000 10000
F 9500 19500
G 8000 27500


Yours hopefully......

 

[FoFa]

OK, I did this many years ago, and I can't find that DB, but basically it worked this, so maybe someone else can supply input if I make a mistake.
Define a function using your value as an input parameter (Fcast), and in the function define a static variable to hold the total (static does not lose it's value between calls) not sure what a static variable is called or defined as today (I think it was version 2.x I did this).
Add your new value to the variable, and send that value out via the function name.
You can use it (the function) in a query and do not have to use a MakeTable query in that case.
Sorry I am not more help in this.

 

[SBM]

FoFa, thanks for your help.

I am working with A97 and am new to defining functions etc. Can you (or anyone) expain a little bit more.

Much appreciated.

 

[FoFa]

Use your VB HELP in your version, but basically it would be along these lines:
QUERY defined new column named CumTot passing Fcast as parameter:
CumTot: CalcCumTot([Fcast])

FUNCTION in a module assumes long value in and out:
function CalcCumTot(iValue as long) as Long
Static new CumTotal as long
CumTotal = CumTotal + iValue
CalcCumTotal = CumTotal
end function

 

[SBM]

FoFa, fantastic !!! This works just fine !

Thanks

 

[poet1234]

Hi All,

I was searching for this very thing, and the function works great, but I am having two issues that I'd like to ask about.

1. Each time I run the query, the numbers that the function returns increase. For example, the first running of the query produces these results:

Month Budget Forcast (cumulative columnn)
Oct $943,910.41 $943,910.41
Nov $943,910.41 $2,421,145.00
Dec $943,910.41 $3,521,404.00

The second running of the query produces these results:

Month Budget Forcast
Oct $943,910.41 $21,455,702.00
Nov $943,910.41 $22,932,937.00
Dec $943,910.41 $24,033,196.00

and so on...In fact, the numbers in the Forcast column (the column using the function) change if I even put my cursor on that field.

2. My other problem is that the function returnd #Error where I don't have any data in the field that I'm cumulating.

Is there any help for me (or this problem)?

Thanks