Counting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bunky - 28 Dec 2007 21:29 GMT

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?

Reply to this Message

KARL DEWEY - 28 Dec 2007 21:39 GMT

Post your SQL.

Signature

KARL DEWEY
Build a little - Test a little

> 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?

Reply to this Message

Bunky - 28 Dec 2007 21:43 GMT

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.
>
[quoted text clipped - 3 lines]
> > 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?

Reply to this Message

KARL DEWEY - 28 Dec 2007 23:01 GMT

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];

Signature

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
[quoted text clipped - 18 lines]
> > > 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?

Reply to this Message

John W. Vinson - 28 Dec 2007 21:50 GMT

>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]

Reply to this Message

Bunky - 31 Dec 2007 21:31 GMT

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!

> >I have a data element in a table named Results. This is defined as a number
> >field. The Values go from 0 to 11.
[quoted text clipped - 7 lines]
>
> John W. Vinson [MVP]

Reply to this Message