Well, this is just by changing this to a sub select, but it should do it
for you.
Open a new query window with 'Create Query in SQL View'.
Copy this complete query to this window.
Click on the Run SQL Direct button
SELECT
"tmp1"."ContactRecords" AS "ContactRecords",
"tmp1"."ContactNames" AS "ContactNames",
"tmp2"."AverageAge" AS "AverageAge",
"tmp2"."fossSuptCnt" AS "fossSuptCnt"
FROM
( SELECT
COUNT( "tbl_BizContact"."biz_id" ) AS "ContactRecords",
COUNT("tbl_BizContact"."biz_name" ) AS "ContactNames"
FROM "tbl_BizContact"
HAVING COUNT("tbl_BizContact"."biz_id" ) IS NOT NULL
AND COUNT("tbl_BizContact"."biz_name" ) IS NOT NULL
) "tmp1"
,
(SELECT
AVG( "tbl_Survey2006"."biz_age") AS "AverageAge",
COUNT( "tbl_Survey2006"."foss_supt_biz" ) AS "fossSuptCnt"
FROM "tbl_Survey2006"
HAVING COUNT("tbl_Survey2006"."foss_supt_biz" ) IS NOT NULL
AND COUNT("tbl_BizContact"."biz_name" ) IS NOT NULL
) "tmp2"
Drew
Don Parris wrote:
Greetings,
I have a table with a number of Yes/No fields. I have a total of 655
records. For each field, I want to count the number of 'Yes' values.
In my
query (design mode), I chose the field and the count function. Somehow,
the query is returning counts of way more records than I have. The
numbers
vary, depending on whether I set the criteria to "IS NOT NULL", which
gets
changed to "IS NOT EMPTY".
Here is the SQL statement:
SELECT COUNT( "tbl_BizContact"."biz_id" ), AVG(
"tbl_Survey2006"."biz_age"
), COUNT( "tbl_Survey2006"."foss_supt_biz" ), COUNT(
"tbl_BizContact"."biz_name" ) FROM "tbl_BizContact" "tbl_BizContact",
"tbl_Survey2006" "tbl_Survey2006" HAVING ( ( COUNT(
"tbl_BizContact"."biz_id" ) IS NOT NULL AND COUNT(
"tbl_Survey2006"."foss_supt_biz" ) IS NOT NULL AND COUNT(
"tbl_BizContact"."biz_name" ) IS NOT NULL ) )
These are the results I get:
Biz_id = 413328
BizName = 410058
Active = 61304
Can anyone help me get this query accurate?
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
