Weighted Calculations on the Group Level

S

SharonInGeorgia

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?
 
C

caloy

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.
 
S

SharonInGeorgia

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?
 
C

caloy

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
 
S

SharonInGeorgia

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top