Nested Subquery In Access Alias Causing "enter Parameter Value"

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 subquer

Solution 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
              )
groupby s.nightid;

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

Solution 2:

Your subquery can only see one level above it. so Night.NightId is totally unknown to it hence why you are being prompted to enter a value. You can use a Group By to get the value you want for each NightId then correlate that back to the original Night table.

Select * 
From Night
left join (
    Select  N.NightId
        , sum(IIF(S.IsDouble,1,0)) as [Number of Doubles]
    from Night N
    inner join Score S
        on S.NightId = S.NightId
    groupby N.NightId) NightsWithScores
on Night.NightId = NightsWithScores.NightId

Because of the IIF(S.IsDouble,1,0) I don't see the point is using top.