Calculation Field Average of Found Values
I have a database for school assignments and I am stuck on the following issue:
I have a table of Assignments where each student is given a grade of 0, 1, 2, or 3. Each assignment is also given a category, such as "Understand", "Apply", "Create", etc. There is also a table of Skills, where each Student gets a final assessment on that particular Skill. For every Skill, there are multiple Assignments, and each Assignment having only 1 Category.
What I want to do is have a report that averages a Student's score for each Category. For instance, Joe Smith is assigned Science Skill One. For that skill there are 3 "Apply" Assignments, and 2 "Understand" Assignments. I want to see what Joe Smith's average is for Apply, and for Understand.
I have fields set up in a related "Report Table" to calculate the average for each of these Categories, yet I am having trouble with the calculation. I know I'm missing a step to filter the values. Below is an example of my calculation:
If ( Assignment Registration::Category = "Understand" ; Average ( Assignment Registration::Mastery Level; ) )