Number of Students Completing a Qualification for a Date Range

[unhinged]

G'day All,

I have to create a report which shows how many qualifications were
awarded to students in a given year. A qualification is deemed to be
awarded to a student if the student has achieved a passing grade for
all of the subjects defined as part of that qualification; a student
can study for more than one qualification but will only ever be
studying for one qualification at a given point in time.

I have worked out how to generate a list of student records showing
each of the qualifications they have studied, along with the date when
they finished studying for that qualification. But since a student
does not always complete the course before finishing their study, I
need to verify that a passing grade has been awarded for all of the
subjects required by that qualification.

I have a query which will show me whether or not a single student has
passed all of the required subjects, thanks to assistance from Allen
Browne:

SELECT tSubjects.SubjectID, tSubjects.SubjectCode,
tSubjects.SubjectName, tSubjects.SubjectAICLCode,
jQualificationSubjects.QualificationID, Exists (SELECT TOP 1
tStudentResults.StudentID FROM tStudentResults WHERE
((tStudentResults.SubjectID = tSubjects.SubjectID) AND
(tStudentResults.StudentID = 1) AND (tStudentResults.QualificationID =
9) AND (Iif((Grade <> "Fail" or IsNull(Grade) or Grade = "" or Grade =
"N/A") AND (Competency = "C" or Competency = "RPL" or Competency = "N/
A" or IsNull(Competency) or Competency = "" or Competency = "EXM"),
"Pass", "Fail") = "Pass")) ORDER BY tStudentResults.StudentID) AS
HasPassed
FROM tSubjects INNER JOIN jQualificationSubjects ON
tSubjects.SubjectID = jQualificationSubjects.SubjectID
WHERE (((jQualificationSubjects.QualificationID)=9));


What I need to do is have this work for all students rather than
needing to use a parameter to define the studentID and the
QualificationID.


The relevant tables and queries are:

tSubjects (SubjectID, SubjectName, etc)
tQualifications (QualificationID, QualificationCode,
QualificationName, etc)
jQualificationSubjects (SubjectID, QualificationID) 'A subject can
be part of several qualifications
tStudentResults (StudentID, SubjectID, QualificationID, Grade,
Competency, Mark, TermAwarded, etc)
sqDistinctStudentCOENumbersWithEndDate (StudentID, QualificationID,
EndDate, COENumber)


I am using sqDistinctStudentCOENumbersWithEndDate as my list of
students that need to show that all of the subjects for the
qualification have been completed.

Any ideas?

 

[Michael Gramelspacher]

I am not sure and have not tested this, but I think something like
this will tell you which students have qualified for what. It is just
an idea?

SELECT a.student_id,
b.qual_id
FROM Student_Classes_Passed AS a
INNER JOIN Qualification_Subjects AS b
ON a.subject_id = b.subject_id
WHERE a.subject_id IN (SELECT subject_id
FROM Qualification_Subjects
WHERE qual_id = b.qual_id)
GROUP BY a.student_id,b.qual_id
HAVING COUNT(* ) = (SELECT COUNT(* )
FROM Qualification_Subjects
WHERE qual_id = b.qual_id);