I am familiar with Access only in passing, so the following query may not compile correctly. But the idea is that you need to join on the PupilID as well.
INNER JOIN SUBJECTLIST sl ON
sl.SubjectID = ps.SubjectId AND sl.Subject = ps.Subject
INNER JOIN TARGETS ts ON
ts.PupliId = ps.PupilID AND ts.TargetType = sl.TargetType