Can't average blocks of weighted grades in report (1 Viewer)

[bremersj]

I'm having a problem getting a report textbox to display the average value that I want.

Background:
I have a DB that tracks student grades. The course is organized into blocks, with several tests in each block. Students can take the same test multiple times, in cases of failure, or if they get rolled back in class. The DB tracks whether or not each test is a retest or audit.

The tests are also weighted. For example, the Geo Quiz could be worth 10% of the grade for a block, and the final exam 90%.

Goal:
My report needs to display the class number, student name, block, test, and associated scores; it needs to show the total grade for each block; and it needs to display the student's average grade across all blocks. I need to be able to generate the report at any point in the course, not just after all blocks and tests have been completed.

Setup:
Since I want to display the assigned grade, but calculate the weighted grade into the block grade, I set up a query (qryWeightedGrades) to calculate the weighted grade for each test (e.g. Score of 98%, weighted at 10% of block, results in a 9.8 for the weighted grade). When it comes to calculating the block grade, I just sum the weighted scores.

In the report (based on qryWeightedGrades) I have the groupling levels set up for Class#, Student name, then Block; the test name, score, weight, retake info, audit info, etc. appears together on a row.

Problem:
What I can't figure out how to do, is get the average of all the block grades. I can't get the textbox in the Student Name group level footer to average the Block grades in the Block group level footer, without getting an error. I've thought of creating another query that averages the block grades for each student, but I have no idea how to incorporate that into the footer of the Student Name group level.

Here's an example of what I want the report to look like:

Class#: 13010
Student Name: Smith, Peter
Block: WF101 (4 tests in block)
....Test: Quiz1, Score: 95, Weight: 10
....Test: Quiz2, Score: 90, Weight: 10
....Test: Essay, Score: 80, Weight: 40
....Test: Final Exam, Score: 85, Weight: 40
Block Grade: 85

Block: WF201 (3 tests in block)
....Test: Geo Quiz, Score: 75, Weight: 20
....Test: Geo Quiz, Score: 80, Retake <---Retake, so score doesn't count
....Test: Essay, Score: 98, Weight: 30
....Test: Final Exam, Score: 100, Weight: 50
Block Grade: 94.4

Block: WF301 (4 tests in block)
....Test: Brief1, Score 90, Weight: 20
....Test: Brief2, Score 92, Weight: 20
Block Grade: 96.4 <--Block incomplete; assumes 100% for rest of tests

Student's average grade: 91.93 <--Getting this # is giving me problems

Class average grade: ##.##

Any help you can provide would be appreciated. I hope this post isn't too long, I was trying to be as thorough as needed to get a helpful response.

Thanks.

-Steve

 

[Pat Hartman]

aggregate functions in form sections cannot refer to controls. You have to repeat the calculation. So

=Avg(Score * Weight)

The problem will be with the partial block. If you want to assume 100% for the missing grades, you'll need to create records with those grades. what is wrong with simply averaging the entered grades? People would be happier to see their grade rise as more scores are entered rather than fall as it almost surely would with your method.

 

[bremersj]

Thanks for the response!

That's what I suspected. I made a complicated set of nested queries to get the numbers I wanted. Perhaps I should go with your simpler solution, though.