Access Dcount using in nested query

Question

I have a nested query that executes fine until i try to roll it up one more time. The sub query rows consist of a Name and response values (about ten columns) of either "M" or "N".  I am trying to summarize the sub query by person and count of M and N values for each of the ten columns.  I can only get the Dcount to count total reponses; not conditionally M or N.  When i attempt to use DCOUNT I get an error message 'jet cannot find C' (which is my subquery) what am i doing wrong please?   (I have tried every combination of single quote double quotes, etc) My desired result would look like:

Name1         5 (C.BlindYOpY=N)

Name2         2 (C.BlindYOpY=N)

Name3         10 (C.BlindYOpY=N) 

SELECT  C.[Name], Dcount("C.BlindYOpY","C","C.BlindYOpY=N") AS YNMatch

FROM (SELECT B.[Name], B.Y, O.Y, IIf(IsNull(B.Y) And IsNull(O.Y), Null,IIf(B.Y=O.Y,"M","N")) AS BlindYOpY...) AS C

Answer 1

Your subquery only exists within the context of your query.  You would have to have the subquery saved as a query in order to use it with the dcount function.

Can you post the entire query string instead of an excerpt.  You might be able to get the result you want with

SELECT C.[Name]

, Abs(SUM(C.BlindYOpY = "N") as YNMatch

FROM (SELECT B.[Name], B.Y, O.Y, IIf(IsNull(B.Y) And IsNull(O.Y), Null,IIf(B.Y=O.Y,"M","N")) AS BlindYOpY...) AS C

GROUP BY C.[Name]

If you prefer you can use a different expression to get the count.  Count counts the presence of a non-null value, so return null if the comparison expression is false and any value you want if the comparison expression is true.

, Count(IIF(C.BlindYOpY = "N",1,Null)) as YnMatch

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County