Nested subquery in Access alias causing "enter parameter value"

Question

I'm using Access (I normally use SQL Server) for a little job, and I'm getting "enter parameter value" for Night.NightId in the statement below that has a subquery within a subquery. I expect it would work if I wasn't nesting it two levels deep, but I can't think of a way around it (query ideas welcome).

The scenario is pretty simple, there's a Night table with a one-to-many relationship to a Score table - each night normally has 10 scores. Each score has a bit field IsDouble which is normally true for two of the scores.

I want to list all of the nights, with a number next to each representing how many of the top 2 scores were marked IsDouble (would be 0, 1 or 2).

Here's the SQL, I've tried lots of combinations of adding aliases to the column and the tables, but I've taken them out for simplicity below:

select Night.*
    , 
    ( select sum(IIF(IsDouble,1,0)) from 
            (SELECT top 2 * from Score where NightId=Night.NightId order by Score desc, IsDouble asc, ID)
    ) as TopTwoMarkedAsDoubles
    from Night

Answer 1

This is a bit of speculation. However, some databases have issues with correlation conditions in multiply nested subqueries. MS Access might have this problem.

If so, you can solve this by using aggregation with a where clause that chooses the top two values:

select s.nightid,
       sum(IIF(IsDouble, 1, 0)) as TopTwoMarkedAsDoubles
from Score as s
where s.id in (select top 2 s2.id
               from score as s2
               where s2.nightid = s.nightid
               order by s2.score desc, s2.IsDouble asc, s2.id
              )
group by s.nightid;

If this works, it is a simply matter to join Night back in to get the additional columns.