Dealing with NZ (1 Viewer)

drisconsult

Drisconsult
Local time
Today, 12:50
Joined
Mar 31, 2004
Messages
125
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
 

Attachments

  • Form12.png
    Form12.png
    42.8 KB · Views: 201
  • Form14.png
    Form14.png
    44.4 KB · Views: 154

Ranman256

Well-known member
Local time
Today, 08:50
Joined
Apr 9, 2015
Messages
4,337
the AVG function (in the query) will count and give you the average.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:50
Joined
Feb 28, 2001
Messages
26,996
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

Drisconsult
Local time
Today, 12:50
Joined
Mar 31, 2004
Messages
125
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

Drisconsult
Local time
Today, 12:50
Joined
Mar 31, 2004
Messages
125
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
 

Attachments

  • F01.png
    F01.png
    48 KB · Views: 203
  • F02.png
    F02.png
    35.6 KB · Views: 226
  • F03.png
    F03.png
    47.6 KB · Views: 235
  • F04.png
    F04.png
    26.8 KB · Views: 211
  • F05.png
    F05.png
    31.6 KB · Views: 202
  • F06.png
    F06.png
    39.6 KB · Views: 155
  • F07.png
    F07.png
    43.1 KB · Views: 234
  • F08.png
    F08.png
    55.7 KB · Views: 228

Users who are viewing this thread

Top Bottom