Running Total Query??

[kwooten]

I am trying to rebuild a query I crashed yesterday. While doing some research in an effort to make it better I am looking for some help. The goal is to create a month to month "running total" rate. In the past I made 2 seperate crosstab queries so i would have my individual date data, then I would create a sum function to sum each month. However, what I would like to do is show month to month growth in the rate.

The formula for the rate is Total X/Average Y. I did some research and thought a DSum may be the answer; at least for the Total X. Not sure how to incorporate the Average Y.

So for January 2012 I would have, for example, 5% with a formula of 10/200.. February 2012 raw data may be X=15 and Y=100 so for February 2012 I would need my formula to be 25 (Total of X; 10 and 15) /150 (Average of Y; 200 and 100)..

Any Ideas?

[kwooten]

Just figured out DSum doesnt work well for Total X but DCount seems to.

[orange]

Can you lay out an example that shows both your X and Y?
Can you give X and Y the actual names and some of the real values in order to clarify what
a month to month "running total" rate really means in your situation?

[kwooten]

I got the DCount figured out for X and I have the average for Y... Im gonna try another method to run the rates. Running Total month to month rate would show growth so January may be 5% then add in for February and the percent may increase to 7% and so on...

[orange]

I'm not sure what you're doing but I did create the routine below to try a few things. It may be useful but as I say, I'm not clear on what you really are trying to do.

Sub testRunningAvg()
Dim Tot As Integer
Dim i As Integer
Dim x As Integer
x = 100
Tot = 0
Dim Pct As Double
Dim M(11) As Integer
M(0) = 30
M(1) = 20
M(2) = 10
M(3) = 25
M(4) = 35
M(5) = 50
M(6) = 10
M(7) = 35
M(8) = 40
M(9) = 25
M(10) = 15
M(11) = 90

For i = 0 To 11
' Tot is the total of the M(i)s up to this value of i
'TotPct is the Number of (i *100) + 100 (to make up for the initial 0 address)
Tot = Tot + M(i)
Debug.Print "i  " & i & "  M(i)  " & M(i) & "  Pct  " & (M(i) / 100) * 100 & " Running Numbers  " & Tot & "  Tot Pct  " & x + (100 * i) & "  Tot/TotPct = " & Tot / (x + (100 * i)) * 100
Next i
End Sub

[kwooten]

attached is the running total peice.. when i add in the DIV1 code it doesnt give me the running total per code.. it gives me the same total for every code.

Thanks,

[kwooten]

On this same DB can you do a running average? I cant get it to work. I think i have to incorporate a count first. I am trying to do the average of the empno.

[orange]

Did you run the procedure I posted?Any comments?

I just looked at your mdb. Where does "turnover" come from?
I don't see Running Totals , Rates...Average..

[kwooten]

not yet.. i got pulled into something else.

For some reason this DB isnt showing all the Objects. I have to tell it to show all object. Turnover comes from the Terms Tbl. The turnover qry does have the running totals. I cant get to the rate yet because I cant seem to get the running turnover by DIV 1 to work and I cant get a running average to work either. If i could get those 2 pieces then the rest is easy. Im running out of ideas beyond take the raw data and dumping it into excel which is not what i want to do.