Solved Get the AVG of a COUNT in Access 2010

Microsoft Office 2010 professional
August 13, 2013 at 08:50:40
Specs: Windows XP
I'm in a report, collecting counts of different case types for individual employees. Each employee has a total number, which is then broken into three categories and this report is run on a weekly basis. Right now, everything is working fine but management wants an average of each employee's week, vs the overal count of all cases worked divided by the number of employees.

So, Jane Doe's report would look like this:
Mon 3
Tues 4
Wed 3
Thurs 5
Fri 2
avg 3.4

Total all employees 127
Num employees 26
Avg 4.88

So Jane here is a little behind the others.

What I've been trying is to use the Avg function like this ='avg([AccessTitaksDate Taken2]) (which is the name of the field with the employee's totals) and ='avg(count([Date Taken]) and avg(Count([AccessTotalsDate Taken2])). I am persistently getting the same error message:

Cannot have aggregate function in expression (Avg(Count([AccessTotalsDate Taken2])))

Short of creating a module, I'm not sure what else to do. This HAS to be possible, right? So what should I do now?


See More: Get the AVG of a COUNT in Access 2010

Report •


#1
August 14, 2013 at 07:48:48
✔ Best Answer
To get an average for each employee, you will need to create 3 seperate fields in the footer of each group. One field will need to be a count of the number of records in that group. The second will need to be the sum of the field being counted. The third field in the footer will be the second field you created in the footer devided by the first field.

For example:

The first field ([AccessTotalsCASE_ID]) will have this formula in it's control source:
=Count([CASE_ID])

The second field ([NEW_TEXT_BOX]) will have this formula in it's control source:
=Sum([EMPLOYEE CASES])

The Last field will have this formula in it's control source:
=[NEW_TEXT_BOX]/[AccessTotalsCONT_ID]

Then you will need to do the same thing for the whole report so you have an average for all employee's so you have something to compare each employee to. You can hide the detail so the report is cleaner.

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •
Related Solutions


Ask Question