Link to home
Start Free TrialLog in
Avatar of MoriPi
MoriPi

asked on

MsAccess bug? or query wrong?

Performing a query, I need a sum of counts of tables.

Query:
SELECT DISTINCT (      SELECT COUNT ([CSTLINKMEMBERS1])
                                    FROM CSTLINKMEMBERSCHECKED
                                    WHERE ((CSTLINKMEMBERS1)=0)
      )
      AS PASSED1
FROM CSTLINKMEMBERSCHECKED;

The subquery DOES return "0":  SELECT COUNT ([CSTLINKMEMBERS1])  FROM CSTLINKMEMBERSCHECKED WHERE ((CSTLINKMEMBERS1)=0).
I would expect this to return "0", would you not?
However it returns no rows at all! its frustrating!

Note: there are times I need to test over 18 fields, so the following query will not work, because of the annoying "Query is too Complex" (one table per field).
SELECT COUNT ([a.CSTLINKMEMBERS1]) AS PASSED1,
FROM
(SELECT CSTLINKMEMBERS1 FROM CSTLINKMEMBERSCHECKED WHERE CSTLINKMEMBERS1=0) a,
ASKER CERTIFIED SOLUTION
Avatar of jrb1
jrb1
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MoriPi
MoriPi

ASKER

ommm *worships the ground jrb1 walks on*
bless you my child