I want to retreive student prerequisite for a particular student who wish to take a c

[AnkitBhangdia]

Query:

SELECT CP.CoursePrerequisite AS [missing prereq]
FROM CoursePrerequisite AS CP
WHERE (((CP.CourseCode)="EGCE 308")
AND (CP.CoursePrerequisite) NOT IN (SELECT StudentCourse.CourseCode FROM StudentCourse WHERE cwid = 987654321));

This query works fine if student has not completed none of the prerequisite required for the course, however if the student has completed atleast one prerequisite for the course then the query is returning wrong result: it's returning the result set as all the prerequisite for that course.

This query was working fine on SQL SERVER 2016 when I tried it on my Professor's machine, however when I'm trying to run the same query with few modifications it is returning wrong results. I've tried making my ACCESS database ANSI92 compatible but despite that I'm getting wrong results.

Please advise/assist at your earliest as I've a deadline to meet.