Counting

[Bunky]

I have a data element in a table named Results. This is defined as a number
field. The Values go from 0 to 11.
The problem is I need to count a certain value but sometimes that value has
not been chosen and that, of course, returns nothing. I would like it to
return the result value I am looking for and a '0'. Any ideas?

 

[KARL DEWEY]

Post your SQL.

 

[Bunky]

SELECT Count([Distinct Waitlist Desired].Results) AS CountOfResults,
IIf([CountOfResults] Is Null,0,[CountOfResults]) AS ZeroResults, [Result
Table].[Result number]
FROM [Result Table] LEFT JOIN [Distinct Waitlist Desired] ON [Result
Table].[Result number] = [Distinct Waitlist Desired].Results
WHERE ((([Distinct Waitlist Desired].Preferred)='G' Or ([Distinct Waitlist
Desired].Preferred)='S' Or ([Distinct Waitlist Desired].Preferred)='P' Or
([Distinct Waitlist Desired].Preferred)='C' Or ([Distinct Waitlist
Desired].Preferred)='M') AND (([Distinct Waitlist Desired].[Date Worked])
Between [Begin Date:] And DateAdd("d",1,CDate([End Date:]))))
GROUP BY [Result Table].[Result number]
HAVING ((([Result Table].[Result number])=4));

The dates I am using are a begin of 11/01/07 and an end of 12/01/07.

 

[John W. Vinson]

I have a data element in a table named Results. This is defined as a number
field. The Values go from 0 to 11.
The problem is I need to count a certain value but sometimes that value has
not been chosen and that, of course, returns nothing. I would like it to
return the result value I am looking for and a '0'. Any ideas?

You may need a little twelve-row auxiliary table with one record for each
value from 0 through 11. If you Left Join this table to your Results table and
use this query as the basis for your count you should get the desired result.

John W. Vinson [MVP]

 

[KARL DEWEY]

As John suggested I filled Results table and removed criteria.

SELECT [Result Table].[Result number], Sum(IIf([Results] Is Not Null,1,0))
AS [Result Count], Sum(IIf([Results] Is Null,1,0)) AS ZeroResults
FROM [Result Table] LEFT JOIN [Distinct Waitlist Desired] ON [Result
Table].[Result number] = [Distinct Waitlist Desired].Results
WHERE ((([Distinct Waitlist Desired].Preferred)='G' Or ([Distinct Waitlist
Desired].Preferred)='S' Or ([Distinct Waitlist Desired].Preferred)='P' Or
([Distinct Waitlist Desired].Preferred)='C' Or ([Distinct Waitlist
Desired].Preferred)='M' Or ([Distinct Waitlist Desired].Preferred) Is Null)
AND (([Distinct Waitlist Desired].[Date Worked]) Between [Begin Date:] And
DateAdd("d",1,CDate([End Date:])) Or ([Distinct Waitlist Desired].[Date
Worked]) Is Null))
GROUP BY [Result Table].[Result number];

--
KARL DEWEY
Build a little - Test a little

SELECT Count([Distinct Waitlist Desired].Results) AS CountOfResults,
IIf([CountOfResults] Is Null,0,[CountOfResults]) AS ZeroResults, [Result
Table].[Result number]
FROM [Result Table] LEFT JOIN [Distinct Waitlist Desired] ON [Result
Table].[Result number] = [Distinct Waitlist Desired].Results
WHERE ((([Distinct Waitlist Desired].Preferred)='G' Or ([Distinct Waitlist
Desired].Preferred)='S' Or ([Distinct Waitlist Desired].Preferred)='P' Or
([Distinct Waitlist Desired].Preferred)='C' Or ([Distinct Waitlist
Desired].Preferred)='M') AND (([Distinct Waitlist Desired].[Date Worked])
Between [Begin Date:] And DateAdd("d",1,CDate([End Date:]))))
GROUP BY [Result Table].[Result number]
HAVING ((([Result Table].[Result number])=4));

The dates I am using are a begin of 11/01/07 and an end of 12/01/07.

Post your SQL.

 

[Bunky]

Thank you both! I knew of that fix but 1) did not want to have to add
another table and 2) had trouble getting it working correctly. It is working
to the 'T' now.

Have a Happy New Year!