Biz_id (PK)
BizName (Text)
Active (Boolean)
On 8/29/06, Andrew Jensen <[EMAIL PROTECTED]> wrote:
*Chuckling*...ok, it's been a long day.
Try this one last time.
You will need to break this select statement apart into sub selects to
get the correct answers.
Your having clauses are most likely not correct, or not doing what you
think they are.
A few mor assumptions here -
Are you trying to count the following:
All records in tbl_BizContact and the column biz_id the primary key?
All records in tbl_BizContact where biz_name IS NOT NULL ?
All records in tbl_Survey2006 where foss_supt_biz is TRUE?
The average of all values in the column tbl_Survey2006.biz_age?
Thanks for the input. We can ditch the primary key - I was just throwing
that in for test purposes. I want to count the total number of records, and
then count all the records that are true for each field in the query. So
when I am done, I should have something to the effect of:
Total Biz | Active | Field2 | Field3 | etc.
657 | 307 | 125 | 40 | etc.
I did this once before, but due to an anomaly in my Calc2Base conversion
(copied the data incorrectly, apparently), had to rebuild the tables and
queries. And I have no clue what I did before that made it all work. :-(
If the answer to all of the above is YES then this is actually a query
made up of 4 sub-select statements. Each getting the one aggregate
value, as each could run across a variable number of records.
SELECT
"tmp1"."ContactRecords" AS "ContactRecords",
"tmp2"."ContactNames" AS "ContactNames",
"tmp3"."AverageAge" AS "AverageAge",
"tmp4"."fossSuptCnt" AS "fossSuptCnt"
FROM
( SELECT
COUNT( "tbl_BizContact"."biz_id" ) AS "ContactRecords",
FROM "tbl_BizContact"
) "tmp1"
,
(SELECT
COUNT( "tbl_BizContact"."biz_name )
FROM "tbl_BizContact"
WHERE "biz_name" IS NOT NULL
) "tmp2"
,
(
SELECT
AVG( "tbl_Survey2006"."biz_age") AS "AverageAge",
FROM "tbl_Survey2006"
) "tmp3"
,
(SELECT
COUNT( "tbl_Survey2006"."foss_supt_biz" ) AS "fossSuptCnt"
FROM "tbl_Survey2006"
WHERE "foss_supt_biz" = TRUE
) "tmp4
This query gives me an "unexpected $end..." error.
