subquery syntax (2007)

Topic

New Reply

I am trying to write the following query:

SELECT tblStudents.txtName, tblStudents.StudentId
FROM tblStudents
WHERE not exists (SELECT fidName
FROM tblAttendance
WHERE fidClass=7);

This part gives me 201 records :
SELECT tblStudents.txtName, tblStudents.StudentId FROM tblStudents

this part gives me 99 records:
SELECT fidName FROM tblAttendance WHERE fidClass=7

How do i put it together to get the names o the 102 people who are not in class 7
fidname is the foreign key of studentID

Thanks

Reply | Quote

Replies

Try

SELECT tblStudents.txtName, tblStudents.StudentId
FROM tblStudents
WHERE tblStudents.StudentId Not In (SELECT fidName FROM tblAttendance WHERE fidClass=7);

Reply | Quote

I had tried that and I get no records even though the two parts of the query work. Still no records. attached is the back end zipped if that helps
Thanks

Reply | Quote

I cannot open .accdb files, sorry. I don’t have Access 2007.

Reply | Quote

Sorry her it is as an mdb

Reply | Quote

The problem is that tblAttendance contains records with null values for fidName and/or fidClass. You should remove those and make the combination of fidName and fidClass the primary key of tblAttendance to prevent this from happening in the future.
Once you have removed the incorrect records, the query returns the records you want. See attached version.

Reply | Quote

thanks. Will try to figure out whay that is true when I can think

Reply | Quote