Averaging Averages

[Kylep]

My database contains thousands of quality surveys, each with 5 Questions. I have a query which groups by agent name so i can find out the average for each rep, including the average for each question and the new field to average all 5 Questions.

I'm now working on a report to show this data, and in the report I am giving a summary tab for the team. This brings some questions about how access does the averaging.

The control source to summarize each question looks like this:
=IIf(IsNull([avgofQ1]),"",Avg([avgofQ1]))

This reads as if access is averaging the average of Q1, which would be the incorrect way to calculate it (since different agents get a different # of surveys.) Am I correct in reading it this way, and if so is there a way to adjust for it?

Also, the Avg Score control source looks like this:
=IIf(IsNull([avgscore]),"",Avg([avgscore]))

This basically would be averaging an average of an average, which is really bad.

Any thoughts on how to improve this?

 

[Pat Hartman]

This statement is incorrect. It will result in a string rather than a numeric value.

=IIf(IsNull([avgofQ1]),"",Avg([avgofQ1]))

- The problem is the "". This is a zero length STRING! Access looks at the statements and says, "hum, the programmer must want a string returned rather than a number" NEVER use "" when you really mean null.

Also, the IIf() is totally unnecessary. a simple Avg([avgofQ1]) will return the correct average. Aggregate functions properly ignore null values. So if there are 5 items and one is null, the divisor will be 4 NOT 5.