"Will" <Wi*****************@hotmail.com> wrote in message
news:37*************@individual.net...
I have a 3 main tables, tblStudent, tblEnrolled and tblCourse. I have an
unmatched query which will tell me which students are not enrolled for a
specific course. I also have an
unmatched query based on tblEnrolled which returns students that are not
on
any courses. What I want is a query which will give me a list of names
along with the courses they have not taken. I have tried but I can't get
the course ID/details to show in my unmatched queries. Thanks in advance
Make a cartesian join between the students and courses tables, then use a
subquery to return presence or absence in the enrollments table.
something like this:
select s.studentName, c.courseName,
iif(
exists(
select * from enrollments as e
where e.studentID=s.studentID
and e.courseName = c.courseName
),"yes","no") as taken
from students as s, courses as c
More elegant would be to feed the above query to a crosstab to give you a
grid showing who is taking what.