ms access sql - select case when in select subquery

Question

I have a table with multiple rows per person and need to get one row per person and the max scores, if a flag is yes, and other things (this is just a snippet of the columns for this example)

Data table

person	qtime	flag	score
Bob	quarter1		4
Bob	quarter2	no	6
Bob	quarter4	no	3
Alice	quarter1	no	4
Alice	quarter2	yes	7
Alice	quarter3	yes	9

select
    person,
    max(score) as maxScore,
    case 
       when person in (select person from data where flag = 'yes') 
          then 1 
          else 0 
    end as flagYes
from
    data
group by 
    person

This would work fine in Microsoft SQL server but in MS Access SQL I get

Syntax error (missing operator in query expression)

I can use aliases in the subquery if that makes things feel like it wouldn't cause problems but they don't make the query get rid of this error.

Answer 1

MS Access doesn't support case expressions. You can use IIF() instead:

select d.person, max(d.score) as maxScore,
       iif(d.person in (select d2.person from data as d2 where d2.flag = 'yes'), 1, 0) as flagYes
from data as d
group by person