Newbie stummped.......

[Muzicmn969]

not sure if this is the proper place to place this question, but here goes....

I am creating a Data base for a private elementry school. Goal is to get each students interim averages, and each report card average. as well as an average per grade...ie: first, second and so on....

I created a table for Student info, Interim Grades, and Report Card Grades.....so far all works well

the problem i am having is kinda unique....

Not all kids in the same grade take the same amount of classes....for instance some might take a total of 5 classes while others take 7....so a simple averaging formula will not work.....

My grade page is designed to have drop down menus next to each class that the child "might " be taking....in other words....there is a screen that allows them to select a child, and select which report card, or interim grade they are entering then a drop down menu next to each class so they can select the grade for the student.....some of these will not be filled in

I am trying to see if there is a way that i can have Access look at the fields (English, Math, etc) and count which fields have a value higher than "0" or higher than "null" and create my divider for the simple average formula....

dont know if i explained it good enough

would appreciate any help with this one

thanks in advance

Rich

 

[Shane_Mc]

Not sure if just using the presence of a grade higher than 0 to be an indication that a class was taken.

Can a child not get zero % in a class?

Can they be absent from a test?

If you think it is what you have is sufficient, all you need is a simple query.

Set the criteria of the grade field to >0. Use a parameter to specify the pupil you want to get your grades for, i.e in criteria field [Enter Pupil Name]. Or to be properly correct, create a form with a combo box on it containing all the names of all the students. Then in your query instead of [Enter Pupil Name] put in [forms]![myFormName]![myComboBoxName] and run the query from the form. This will make sure you are getting correct spelling and all that.

 

[Pat Hartman]

Relational databases do not offer any aggregate functions that operate accross multiple columns of a single row. All aggregate functions operate on a single column down multiple rows. It your data is properly normalized, all grades are in a separate row, then the aggregate function Avg() will work just fine since it ignores nulls.

If your table is NOT normalized, you're on your own. You'll need to build your own function with VBA to count the populated columns and then average their contents.