Unmatched Query Help Wanted

[unhinged]

G'day,

I am trying to match a list of subjects required to a list of student's
results. I want to check programmatically whether or not a student has
achieved a passing grade or better in every subject required to
complete a course.

The tables are (with the key fields and the important data fields):

tStudentResults:
StudentID
SubjectID
CourseID
Competency
Grade

tCourses:
CourseID
QualificationID

tSubjects:
SubjectID
QualificationID


I first create a subquery, sqStudentPassResults:

SELECT tStudentResults.StudentID, tCourses.QualificationID,
IIf(([Competency]="Competent" Or [Competency]="Recognised Prior
Learning") Or ([Grade]<>"Fail"),"Pass","") AS IsPass,
tStudentResults.SubjectID
FROM tStudentResults RIGHT JOIN tCourses ON tStudentResults.CourseID =
tCourses.CourseID
WHERE (((tStudentResults.StudentID)=1) AND
((tCourses.QualificationID)=9) AND ((IIf(([Competency]="Competent" Or
[Competency]="Recognised Prior Learning") Or
([Grade]<>"Fail"),"Pass",""))="Pass"));

As you can see, I have to restrict this query to a single student and
qualification to get the right data set (a student may undertake more
than one qualification).

The "unmatched" query, uqStudentIncompleteSubjects, is this:

SELECT tSubjects.SubjectID, tSubjects.SubjectCode,
tSubjects.SubjectName, tSubjects.SubjectDescription,
tSubjects.QualificationID, tSubjects.SubjectAICLCode
FROM tSubjects LEFT JOIN sqStudentPassResults ON tSubjects.SubjectID =
sqStudentPassResults.SubjectID
WHERE (((tSubjects.QualificationID)=9) AND
((sqStudentPassResults.SubjectID) Is Null));

And again, I have to specify the qualification to limit the data set to
the proper results.




My big problem is, how do I do this in VBA? I want to be able to take a
student record, collect that student's results for a particular
qualification, check to see if all of the subjects for that
qualification are completed successfully, and then allow or deny
certain user actions based on that result.

Any help is much appreciated.

Daniel.

 

[Allen Browne]

Hi Daniel.

The solution to doing all of this in VBA code will be to build a SQL
statement so you can OpenRecordset() and see if there are any units that are
not fulfulled. To do it all in one SQL statement, you will need to use
subqueries. That's essentially an entire SELECT query inside another one. If
subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

I'm not clear about the connection between courses and subjects here (esp.
as tStudentsResults seems to contain both), but the subquery might be
something along these lines:

SELECT tSubject.SubjectID,
EXISTS (SELECT TOP 1 tStudentResults.Grade
FROM tStudentResults
WHERE tStudentResults.StudentID = 999
AND tStudentResults.Subject = tSubject.SubjectID
AND tStudentResults.Grade >= 5
ORDER BY tStudentResults.Grade DESC) AS HasDoneIt
FROM tSubject
WHERE tSubject.QualificationID = "PhD";

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

G'day,

I am trying to match a list of subjects required to a list of student's
results. I want to check programmatically whether or not a student has
achieved a passing grade or better in every subject required to
complete a course.

The tables are (with the key fields and the important data fields):

tStudentResults:
StudentID
SubjectID
CourseID
Competency
Grade

tCourses:
CourseID
QualificationID

tSubjects:
SubjectID
QualificationID


I first create a subquery, sqStudentPassResults:

SELECT tStudentResults.StudentID, tCourses.QualificationID,
IIf(([Competency]="Competent" Or [Competency]="Recognised Prior
Learning") Or ([Grade]<>"Fail"),"Pass","") AS IsPass,
tStudentResults.SubjectID
FROM tStudentResults RIGHT JOIN tCourses ON tStudentResults.CourseID =
tCourses.CourseID
WHERE (((tStudentResults.StudentID)=1) AND
((tCourses.QualificationID)=9) AND ((IIf(([Competency]="Competent" Or
[Competency]="Recognised Prior Learning") Or
([Grade]<>"Fail"),"Pass",""))="Pass"));

As you can see, I have to restrict this query to a single student and
qualification to get the right data set (a student may undertake more
than one qualification).

The "unmatched" query, uqStudentIncompleteSubjects, is this:

SELECT tSubjects.SubjectID, tSubjects.SubjectCode,
tSubjects.SubjectName, tSubjects.SubjectDescription,
tSubjects.QualificationID, tSubjects.SubjectAICLCode
FROM tSubjects LEFT JOIN sqStudentPassResults ON tSubjects.SubjectID =
sqStudentPassResults.SubjectID
WHERE (((tSubjects.QualificationID)=9) AND
((sqStudentPassResults.SubjectID) Is Null));

And again, I have to specify the qualification to limit the data set to
the proper results.




My big problem is, how do I do this in VBA? I want to be able to take a
student record, collect that student's results for a particular
qualification, check to see if all of the subjects for that
qualification are completed successfully, and then allow or deny
certain user actions based on that result.

Any help is much appreciated.

Daniel

 

[unhinged]

YOU LITTLE BEAUTY!!!!!!!

Allen, you are a marvel - typing in almost exactly what you provided
has given me the exact result I need! Thank you!!!!


Just to provide a bit more explanation, a Course is an instance of a
Qualification and occurs between two dates. I actually had to change
the definition of tStudentResults to store the QualificationID so that
I could more closely follow your example; I kept getting a syntax error
when I tried to recreate the sqStudentPassResults query as part of the
correlated subquery and could not find the cause.

A Subject is defined as part of a Qualification, so when
"instantiation" occurs, the course record has access to the list of
subjects required by the qualification. This system needs to follow the
rules outlined by the AQTF, so there's even more stuff to keep track of
but it's not pertinent to this discussion.


Part of my research on this included John Viescas' excellent book,
"Building Microsoft Access Applications" from Microsoft Press (ISBN
0-7356-2039-3) which has a thoroughly daunting section on checking for
prerequisites before allowing a student to register for a course
(starting on page 484). His solution also includes the option for
exempting particular subjects, but the detail in it is far more than I
can spare the time for on my current project. His approach uses a
number of open recordsets, which is something that I wanted to avoid
here. Allen's help on this query allows me to maintain a single
connection and seek through a small (<100) set of records for a false
result on the "HasDoneIt" query field.

Thanks again, Allen!