3 Replies Latest reply on Jan 13, 2012 4:44 PM by JacquiAdler

# Average across matching records

### Title

Average across matching records

### Post

OK, this is probably a real dumb question, but I have been working on databases all day long, and my brain has turned to mush.

I have a database with student records.  Related to this database are several tables with student grades, (one for each subject...long story as to why, but it works for what we need it to do).  Most of the time students only have one grade per subject, but for a couple of subjects, students have two or more grades.  For what we are using our database for, I need to be able to get an average of the grades within each table (subject) for each student.  Is there a simple way to do this that I am totally missing?

• ###### 1. Re: Average across matching records

(one for each subject...long story as to why, but it works for what we need it to do).

Until you want to develop reports from your data such as this average and discover that maybe that it really doesn't work all that well for you after all.... A single table that stores grades for all subjects is a much better design.

Is this an average of just the grades for one student from one subject table or is it a GPA type calculation that must include data from all the tables?

Averaging data from just one table, is simple, you can use either the Average function like this: Average ( gradeTable::Grade ), provided that "Grade" is a number field. Or you can define a summary field to compute the "average of" Grade in the grade table and then you can refer to this summary field from the student table to get the average for that student for that grade.

computing the average grade across all subject tables might require a calculation such as:

where sAverageGrade is that average summary field I described earlier and is used in place of grade in any table where you might have more than one grade for a given student in that subject.

• ###### 2. Re: Average across matching records

(one for each subject...long story as to why, but it works for what we need it to do).

Until you want to develop reports from your data such as this average and discover that maybe that it really doesn't work all that well for you after all....  A single table that stores grades for all subjects is a much better design.

Yeah, I know...I feel like I am being a rebel and breaking all the rules!  Normally, I would, trust me.  I have in all of my other databases.  But this is a one-shot-one-use quickie thing I am throwing together and because of the way the data is handed to me, this sadly makes the most sense.

Averaging data from just one table, is simple, you can use either the Average function like this: Average ( gradeTable::Grade ), provided that "Grade" is a number field. Or you can define a summary field to compute the "average of" Grade in the grade table and then you can refer to this summary field from the student table to get the average for that student for that grade.

I tried that, but it didn't seem to work.  Oh, wait...<bangs head into wall> probably should have done it from my master student table, huh?  Did I mention the mushy brain?  Thanks for talking me through it!  Off to try.

• ###### 3. Re: Average across matching records

Sigh.

Yup, huge dork.  Right here.

Totally simple, you were right.  I just wasn't thinking.  Thanks again!  You saved my sanity!