How to get an average of two groups?

[codeliftsleep]

Having some issues with this and I know the answer is really easy, but I can't seem to get it right.

I have a report based on a query where it groups by Employee and also Customer Tier. We have 2 Tiers, 1 and 2. Each employee is responsible for handling a number of clients in each tier, and we then take the average customer score for each employee in the tier(so if an employee has 5 customers in tier 1 and 10 in tier 2, we take the average of the scores for each tier). This part is working fine.

Now the issue I am having is I cannot figure out how to get the Overall Average of both tiers combined. It works properly for each individual tier but I cannot get it working for the both tiers in total. All I basically need to do is to sum all the scores for each employee and divide by the total since the percentages are always taken out of the same denominator(50 points). In effect, the Average of ALL accounts for that employee(I can't average the two tiers together because they don't have equal customers in each tier---one employee might have 9 customers in Tier 1 and 4 in Tier 2...if I average them it comes out wrong because it counts each tier equally)

How can I write a query expression that allows me to do this properly?

 

[isladogs]

Create a new aggregate query but this time omit the tier.
This will give you the average by employee

 

[codeliftsleep]

Create a new aggregate query but this time omit the tier.
This will give you the average by employee

OK I did that and am getting the correct value, but how to add it to the report?

The report is pulling from the original query and when I attempt to add the new query column in the textbox it gives me the pop-up input box asking for a value...

I tried adding it to the original query but then I get a ton of extra rows

 

[Pat Hartman]

Your report needs another level of grouping. Add a group footer for employee. In that footer, you can average everything for the employee. You can also add an average for the entire report in the Report Footer.

Due to the way reports work, the expressions for all three breaks will be identical.

So if the ControlSource in the CustomerTier is
=Avg(SomeAmtField)

Then the controls in the Employee Footer and in the report Footer would be identical. You can just copy the controls and put them into each footer. Access controls their scope so you don't have to do anything.