Grading System Query

[Simon Opare]

Pls. I want to select records of students with gradevalue less than or equal to 6 in both maths and English. Any time i run a query, what i get is that I have some students who have either gradevalue less than or equal to 6 in either maths or English.
However, i want to show only students who have gradevalue less than or equal to 6 in both Mathematics and English Language.
Please Help.
Below is the table relationship and sample records without a query and one with a query.

 

[dhookom]

I didn't see any relationships or table names only sample records.

SELECT *
FROM [Your Table]
WHERE CandidateNumber IN 
(SELECT CandidateNumber
 FROM [Your Table] M
 WHERE M.SubjectID = 2 AND M.GradeValue <6 and M.CandidateNumber = [Your Table].CandidateNumber)
AND CandidateNumber IN 
(SELECT CandidateNumber
 FROM [Your Table] E
 WHERE E.SubjectID = 1 AND E.GradeValue <6 and E.CandidateNumber = [Your Table].CandidateNumber)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016