Count of group averages

[pofe333]

Hello, hopefully a simple question here. I have a report with 6 months of usage for many lines. Each line is separate and I have created a text box that averages their usage for the 6 months in the group header. What I'm trying to do is make a front page listing the number of lines with averages between certain amounts. For example:

Statement | Used
1/09 ....... | 100
2/09 ....... | 300
3/09 ....... | 400
4/09 ....... | 200
5/09 ....... | 600
6/09 ....... | 500

The average would be 350, and my formula for the line is =Avg([Used]). That works fine, but I would like to create a count box at the top of the report that would count all lines with an average below let's say 450. For some reason, I can't seem to get Access to count these. Any suggestions?

 

[Galaxiom]

You cannot do a conditional count under this circumstance.

Add a field to the Record Source query. Set it to show 1 if the condition is met and zero if not. Sum this field to get your answer.

This field need not be displayed on the report.

 

[pofe333]

You cannot do a conditional count under this circumstance.

Add a field to the Record Source query. Set it to show 1 if the condition is met and zero if not. Sum this field to get your answer.

This field need not be displayed on the report.

Thanks. I eventually came to realize this as well. The problem was that I needed to average several groups, and then do a conditional count on them. I ended up just making two reports - one to do the average, one to do the count.