Find record which does not have corresponding record x (1 Viewer)

[mike6271]

Hi

I am having real difficulty here. I have tblStudents (StudentID, Name etc.) and jtblStudentSession (SessionID, StudentID). Can someone please advise how I would query which Students are not registered for Session x. The unmatched records part is simple, what I cant work out is if how to find students registered for some sessions but not this one.

Thank you

Mike

 

[pbaldy]

Try the unmatched query wizard, then add a criteria on that field.

 

[mike6271]

Hi

Thanks for replying

I don't know if I'm making myself clear. I have created the following query.

SELECT tblStudents.*
FROM tblStudents LEFT JOIN jtblStudentSession ON tblStudents.[fldStudentID] = jtblStudentSession.[fldStudentID]
WHERE (((jtblStudentSession.fldStudentID) Is Null Or (jtblStudentSession.fldSessionID)<>3));

However the bold section is meaningless because all students have some record.

I cant think of the criteria which would allow me to select all students who don't have a this particular sessionID but do have others.

 

[pbaldy]

You were clear, but upon reflection I'm not sure my answer would work. I think the session would have to be part of the join, not in the criteria. Here's a better idea I think: create a query that returns all the students who have taken session 3, then use the unmatched query wizard to compare the student table against that query, which would give you anyone who hasn't taken session 3. That would be more dynamic, as you could get the session number from user input.

 

[mike6271]

Thanks Paul you are a genius.

This was actually the data source of my form listbox so you can multiselect students and the added students would jump to another listbox of added students.

You have saved me from a suicide pact with my computer.

Thanks a mill

 

[pbaldy]

No problemo! LOL at the suicide pact. Fake it out. Tell it to jump out the window first, and you'll be right behind it. They fall for that every time.