I got stuck in SQL select that has exactly multiple values

Question

Table name: test
id - student - class
1 - Jane - A
2 - David - B
3 - Jane - C
4 - Nick - A
5 - Nick - B
6 - Smith - C
----------------

Now I want to find out Student name that enrolled both A and C class (must be A and C) the result will show Jane because she enrolled in both A and C

p/s: Im using MS - ACCESS 2007

Answer 1

Group by the students and select only classes Aand C. then take only those students having both entries

select student
from test
group by student
having sum(iif(class = 'A',1,0)) > 0
and sum(iif(class = 'B',1,0)) > 0