CODEWITHSUNDEEP
sqlms-accesssubquery
jawz101
jawz101

Reputation: 91

ms access sql - select case when in select subquery

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.

Upvotes: 1

Views: 308

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

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

Upvotes: 1

Related Questions