Solved Report total count in header and grand total. Avg percent calculation. Grand total employee group count.

[Jomat]

Hello all. I hope someone can help me with this. I am having problems solving the count employee in the report header and avg of percent calculation.
Everything else seems ok. One of my problem is that I can't figure out how to calculate the average of the percent total. I've uploaded my db. When I run the department report, some of my totals are incorrect. I've marked the incorrect totals in red.
1. The score percent field is not a percent field. It is just a regular number field.
2. The hour field is just a number field.
3. My grand total employee count is off. It is counting the trainings and not the Employees.
Thank you ahead of time for any help.

 

[arnelgp]

see the employee count on the report.
what i like to know is how do you:

1. Calculate the Avg % for each Department
2. Calculate the Avg % for each employee

 

[Jomat]

see the employee count on the report.
what i like to know is how do you:

1. Calculate the Avg % for each Department
2. Calculate the Avg % for each employee

I'm not sure what I did. But here is what I tried to do.

I tried to count ([scores]/the grand total]) ) then divide that by the Count([score]/the grand total) * 100 then add the % sign.

Once I typed it out, (the above), it doesn't make sense at all.
I also notice that I am missing a few calculation fields after redo/testing and testing over and over again. It seems Access deletes the calculation fields when I change the footer or header calculations options. If I manually drag the field to the group header (to test it) then re create it again back to the group footer, the field in the header disappears. I have included a screenshot of what I'm trying to explain.
Originally, I have a field set to visible(no) in the group footer (which I notice is not there now).
I'm missing the footer count of the [scores] to the employee field.
Also I think I need to redo the entire report since it seems I lost my trend of thought through the calculation fields. I tried to use the method of adding a field that is =1 to each record and then sum that field as it populates for the department, employees and the training count.

I will look at your example first.

 

[arnelgp]

I tried to count ([scores]/the grand total]) ) then divide that by the Count([score]/the grand total) * 100 then add the % sign.

i did not mean your calculation on the report.
what i meant is what is the "actual" calculation.
what is "grand total"? grand total for each Department or grand Total for the Whole report?

i made another attemp.
please if the calculation is wrong then on an excel sheet put the expected results(Value) for the report.
then upload a snapshot of the excel result.

 

[Jomat]

i did not mean your calculation on the report.
what i meant is what is the "actual" calculation.
what is "grand total"? grand total for each Department or grand Total for the Whole report?

You are very clever!
I didn't think of creating queries, instead I was trying to do the calculations on the report.
I fixed the report. No more percentage, just the avg is fine. That was one of my mistake.
I created all the calculations that I needed.
The average for department, avg for emp.
I created the avg score for all as well.
I used your queries to create the other queries.

1. I don't know how to get avgall from my query to the Grand Total at the end of the report.
2. I don't know how to tally the employee count to the Grand Total at the end of the report.

I've tried to sum over all but get the error that the field is missing.
I've made all the correction Green and left the two fields at the end of the report red. I removed the fields because I don't know how to write the codes for them.

EDITED:
I calculate the avg by summing all the scores then divide by the count of the training.
example:
If test1 is 50
If test2 is 60
Then the average is Test1 + Test 2 = 110
Count the tests which is 2
Divide the total (110) by 2 = 55

 

[arnelgp]

check if the calculation is correct.

 

[Jomat]

check if the calculation is correct.

The employee count is correct. But the total avg scores is off.
I think in your example you are / by the number of employees.
I'm trying to divide the total scores by the number of training.

One of the field in the summary section is called AccessTotalsDailyID. This is the grand total count of the trainings on the report. I try the following but I get an error.

=DSum("AvgDeptScore","qryAvgByDept")/[AccessTotalsDailyID]

Do you know how I can divide by that field.
I have a query called qryAvgAll that shows the avg of all the trainingscores should be 62.9.

 

[arnelgp]

maybe:

=DSum("AvgDeptScore","qryAvgByDept")/DCount("1", "Training_T")

 

[Jomat]

=DSum("AvgDeptScore","qryAvgByDept")/DCount("1", "Training_T")

The dcount is correct. It's the dsum.

If i run the dsum(first part)
i get the total avg from the department which is different from the total of the training records.

The avgdeptscore total is 338.5.
The total score_percentage is 755.
I need the total score points from the training table divide to the training count which is 12.

I also see where my original problem/misunderstanding arise, the field is called scores_percentage but it just records the score point.

 

[arnelgp]

=Dsum("Score_Percentage","Training_T") / DCount("1", "Training_T")

 

[Jomat]

That works!
Can you advise me on what changes I would have to make if I added the criteria to my Report Query?
I understand that the new queries will not work with the following tied to my Report Source Query.

Between [Enter the begin TRAINING DATE to search by?] And [Enter the end TRAINING DATE to search by?]

The above is needed since a user might just want a month or just a year report.

Do I have to recreate individual queries?

I originally left out the criteria above because every time I made changes and test the form, I had to type in two dates such as 01/01/1900 and 01/01/3000 to retrieve all the records.

I've uploaded the working sample db for future reference.

 

[Jomat]

Thank you for all your help. I really appreciate it very much.