?
Solved

MsAccess bug? or query wrong?

Posted on 2006-06-14
3
Medium Priority
?
204 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 1000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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