Weighted Calculations on the Group Level

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My report has
1. Detail
2. First Group: DIV
3. Second Group: REGION
4. Third Group: STATE

My report includes fields directly from the table but it also includes
complex calculations from a query. Everything works great in the Detail
level, however, I am not getting a percentage on the group levels since these
calculations are weighted. The groupings include "First" in the calculations
when I attempt to recreate the calcs for the group level. How can I get the
weighted percentages for my DIVISION and REGION levels?

Is there a way to use variables so that I can recreate the group
calculations without being tied to the Access report structure?
 
SharonInGeorgia,
Firstly, I would think you detail level calculations were also weighted. If
so, you need to apply the same weighting calculation on a summary level (ie.
div, region, state)
ex. detail: SVL = CH20 (calls handled in 20secs) / NCH (no. of calls handled)
Div: SVL = sum (ch20)div level/ sum (nch) div level

Do your calculations in query, then pull the summary data into your report.
Hope this helps you out.
 
Caloyski,

Do I still need to create four queries with the same calculatins (Detail,
Group by Division, Group by Region, Group by State) to accomodate the report?
 
Yes, unfortunately you will have to, since the these are are not straight
averages. The nice thing about this is that you can re-create the summaries
using the same detail reporting query. Just remove the detail field to move
one level up for your summary.

ex. Detail
State Region Div. Agent NCH CH20 SVL

Div: (take out "Agent" field)
State Region Div. sum(NCH) sum(CH20) SVL

....and so on
 
I named each of my fields as follows:

Detail: NetProfit
Division: NetProfit DivTot
Region: NetProfit RegTot
Report Tot: NetProfit GrandTotal

I also pulled the fields from my query that were in the calculations but not
in the reports and set them to invisable. I was able to take advantage of
Access' ability to summed the "straight" numbers and then used the field
names in those fields that required wieghted calculations.

Region Calcs (RT): =([InsideSalesPosRT]-[MdseCostRT])/[InsideSalesPosRT]*100
Div Calcs (DT): =([InsideSalesPosDT]-[MdseCostDT])/[InsideSalesPosDT]*100
 
Back
Top