report grouping headers count
-
This may be outside the scope of this group, but I'm hoping someone can help me. I have designed an Access DB to track our employees' ongoing education credits. It has a report that accepts a date range and lists, by employee, any courses and their credits. The employee name field is used as a grouping header when there is more than one training event for a given employee within the range. I need to calculate the average credits per employee for the date range and show it on the report. I have a running sum field set up to give me the number of total credits, I need the number of unique employees to divide it by. Theoretically, this would be the same as the number of grouping headers on the report, but I can't figure out how to get this value from Access. Anyone have any ideas? Thanks.
-
Doesn't access have an average (avg) function?
-
It does, but my understanding is that it averages the sum total of a given field by the instance count of that field which is not what I want to do. That would give me the average # of credits each event is worth. I want the average # of credits each employee earned. Because a single employee may have more than one event for the range, I need to divide by the number of employees listed, not the number of individual events.
-
I know I've already done that somewhere... What have you tried?
Did you try to place the avg on the footer of the grouping and checking that the avg is right/wrong?
-
Yes, I've tried that. It's wrong. It averages the total number of credits by the number of events, not by the number of employees. The number is too low because some employees attend more than one event.
-
I redid it on one of my reports.
Open the group by wizard in the menu
check display header/footer for the employees group
in the footer add this :
=Average([ColName]) (might me AVG() but my version is french so I can't test).
-
The average function only returns the average of one particular field against itself. This is not what I'm trying to do.
-
The only way I found to count that is to set a global variable and increment it on the group_format event. Then you can set the number manually once you have that number and the running total.
-
That's what I was trying to do but wasn't exactly sure how to code it. But I actually got a better solution on another forum:
Set up a text box in the employee header with a Control Source value of =1 and set the Running Sum property to Over Group. That should get you a counter of employees.
Basically the same idea. Thanks.
-
Tried that... but I couldn't make it work. But as long as you have better luck than me then it's all good
.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply