Dealing with NZ

[drisconsult]

Hello All
I am still trying to resurrect my brain after an absence of 20 years, it is working with a great deal of effort. Here is my next problem. There are 12 compulsory subjects in the UK GCSE curriculum, with the option of two more subjects, which the student can opt into. These are Sex Education and Religious Education. I need to find the average grade after a student has completed their MOCK exams. Here lies my problem There would be students who would have completed 12 subject, or 13 subjects or 14 subjects.
How will I find the average grade for these students who have completed different numbers of subjects? I include a screenshot of the form I use/
Terence Driscoll
London

 

[Ranman256]

the AVG function (in the query) will count and give you the average.

 

[The_Doc_Man]

You suggested NZ as something that might be helpful. You might consider this instead...

Query1:
SELECT StudentID, Count(Grades) as NumGrades, Avg(Grades) as AvgGrades FROM StudentGrades WHERE NOT (Grades Is Null ) ;

Query2:
SELECT ST.StudentID, ST.StudentName, Q1.NumGrades, Q1.AvgGrades
FROM StudentGrades ST INNER JOIN Query1 Q1 ON Q1.StudentID = ST.StudentID ;

Then from query 2 you can fill in the name, number of classes, and grade average.

From the StudentGrades table you can fill in the details of each course.

 

[drisconsult]

You suggested NZ as something that might be helpful. You might consider this instead...

Query1:
SELECT StudentID, Count(Grades) as NumGrades, Avg(Grades) as AvgGrades FROM StudentGrades WHERE NOT (Grades Is Null ) ;

Query2:
SELECT ST.StudentID, ST.StudentName, Q1.NumGrades, Q1.AvgGrades
FROM StudentGrades ST INNER JOIN Query1 Q1 ON Q1.StudentID = ST.StudentID ;

Then from query 2 you can fill in the name, number of classes, and grade average.

From the StudentGrades table you can fill in the details of each course.

Thank you both for your replies. I have been using NZ but without success in finding averages. However I will look into your suggestions and get back to you.
Thank you
Terence

 

[drisconsult]

Thank you both for your replies. I have been using NZ but without success in finding averages. However I will look into your suggestions and get back to you.
Thank you
Terence

I think I should outline the structure of my program so that you can see if I have made any structural errors. I am upoading a series of forms that show how the user should begin when using the program fro the first time.

Thank you both for your replies. I have been using NZ but without success in finding averages. However I will look into your suggestions and get back to you.
Thank you
Terence

Thank you both for your replies. I have been using NZ but without success in finding averages. However I will look into your suggestions and get back to you.
Thank you
Terence

Before attempting your advice on averages, I feel that I should outline the structure of my program. First The teacher enters all relevant data on classes, teachers, subjects and any other information required.
All subject data is entered through combo boxes so that changes can be made whenever necessary. This includes teachers, subjects and year. I am attaching a number of Forms that will hopefully show you how my program works.
F01 is the main menu. I have no problem with Key Stage 3.
F02 is the setup menu where all relevant data is entered.
F03 is the GCSE menu.
F04 is the Subject Data Entry Menu.
F05 is the Teacher Data Entry Menu
F06 is the Teacher and Subject Data Entry Menu. Here the teacher enters the class. Then each student is allocated their subject and teacher for that subject. This only has to be done once a year. All teachers and subjects are inserted through combo boxes.
F07 shows the Form that allows the teacher to enter the grade marks out of 100 and then allocate the new grade number to each subject.
I have used the NZ function to count the grades. And here is my problem. Do I enter a 0 if the student is not doing Religious Education or do I leave it blank? I am doing my NZ calculation on the Form itself. I have tried this through the query concerned without any success. I am getting constant errors.
F08 These are the fields I am concerned with.
Thank you all for your help here
Terence Driscoll
London