Using MAX function to obtain highest value in a Summary Field
I am using a college gradebook application and have a summary field in one of my tables used to compute a total for student participation credit sorted by (unique) student ID. In my table I also have a separate record for each class date. For the summary field to be accurate, I must return the highest running value in the summary field based upon the latest date. I then use the value in this summary field as part of my final student grade calculation in a calculated field I have placed in a table related by student ID. I am finding that the results of my student grade calculation in my calculated field are not accurate because it is using the initial value from my summary field rather than the more accurate value from the latest date.
From my research, I believe I can use the MAX(field) function to properly capture the latest (highest) value from my summary field, but I am unsure as to the syntax when entering into my calculated field formula. My calculated field references the name of the summary field, but I am unsure how to apply the MAX (field) function in my formula.
My current calculated field formula reads as follows but is returning the incorrect lower value from my referenced "Total Class Participation Score" summary field. If my logic is correct, I would want to properly use the MAX(field) function in my formula to return the correct latest date (highest) value.
(((Student Attendance_Class Participation Computation::Total Class Participation Score/270)+ (Interim Quiz #1 Grade * 100) + (Midterm Exam Grade * 180)+ (Interim Quiz #2 Grade * 100) + (Final Exam Grade * 200)+ (Oral Presentation Grade * 150))/10 - Student Attendance_Class Participation Computation::Excessive Absenteeism Penalty)/100
I would appreciate your help in identifying if I am on the right track in my logic described above and, if so, your help in describing how to use the MAX(field) function in a calculated field that references another field. Thanks for your help.