Solved

MsAccess bug? or query wrong?

Posted on 2006-06-14
3
201 Views
Last Modified: 2010-08-05
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,
0
Comment
Question by:MoriPi
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 250 total points
ID: 16908990
how many rows are in CSTLINKMEMBERSCHECKED?  If there are zero rows, your select from the table will return zero rows.
0
 

Author Comment

by:MoriPi
ID: 16909230
ommm *worships the ground jrb1 walks on*
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16911528
bless you my child
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question