Running Sum divided by Month Total

[kwooten]

I am trying to do a running sum divided by the month total. The month total is a baseline and the running sum is for # of hours. I am trying to do cumulative hours per person. I had the Dsum formula working correctly but can seem to get it to divide by the reporting months baseline.. any ideas?

=(DSum("[Training]","Relationship Mgt","[HRP LNAME]='" & [HRP LNAME] & "' AND [Month Number]<=" & [Month Number] & ""))/("[Portfolio Total]","Relationship Mgt","[HRP LNAME]='" & [HRP LNAME] & "' AND [Month Number]<=" & [Month Number] & "")

[pbaldy]

You'd want a separate DSum() for the second value. You have all the parts of one but not the actual "DSum".

[kwooten]

I dont want a running sum for the second part. Just what the value is for the reporting month.. My report is data by month so for example in February i would want the training total hours (January + February)/February baseline (Portfolio Total).. For April i would want (Jan+Feb+Mar+Apr)/April Baseline...

[pbaldy]

Yes, I know what you want. It's still a DSum(), just different criteria.

[kwooten]

i thought if i did a DSum on the second piece then it would give me the running total for Portfolio Totals too... do i need to change to [Month Number]=" & [Month Number]?

[pbaldy]

That's what I would try.

[kwooten]

that seems to have worked... is it better to do DSum in the report or should I do it in the query? It is taking a long time for this report to calculate and run.

[pbaldy]

Domain aggregate functions in queries are notorious performance killers, but having one in the detail section of the report may not help much. You can try using the Running Sum property of a textbox and see if it works for you.