Summing value X in all records based on value Y
I have a database for my classroom and a table that collects and calculates each student's grade. However, this table is for all my classes that I teach.
What I want to do is the following: for each student, I want to calculate their grade and the average grade of their SPECIFIC class so I can graph their performance and compare it to the class' performance.
Now I can easily calculate each student's average grade. Only problem is writing a script that (1) gets the records for all the students in that specific class, and (2) find the overall average of the grade for the class.
Here is what I currently have:
Set Variable [$$TotalGrades; Value:0]
Go to Layout ["Students" (Students)]
Set Variable [$$Classroom; Value: Students::Class]
Go to Record/Request/Page[First]
If[Students::Class = $$Classroom]
Go to Field [ Students::Grade]
Set Variable [ $$TotalGrades = $$TotalGrades + Students::Grade]
Go to Record/Request/Page[Next;Exit after last]
Set Filed [Students::Average Grade; $$TotalGrades/ Get(TotalRecordCount)]
Can someone please tell me what I'm doing wrong? Also another thing to note is that the field 'Average Grade' is in a different layout of the same table, does that change anything?
Thanks in advance! I really need the help :(