Zero counts go AWOL

[Zippy]

Hi,

I have table with just two text fields. These fields are named TestType and
Result.

TestType contains the word Routine along with a number such as Routine 1,
Routine 2 etc.
Result contains one of either Success, Failure, Pending or Invalid.

I am trying to get a count Success and Failure combined (a valid test in my
parlance), a count of Invalids and a count of Pending. This is required for
each TestType ie number of valid tests for Routine 1 would count the number
of successes and failures for that TestType.

I am currently attempting to do this by a query that first asks what Routine
number the counts are required for. This then is used with further count
queries to provide just a single query that I can use on a form without the
need for subforms. This almost works but if I have any of the counts being
0 (zero) I don't get any of the counts displayed.

Can anyone suggest a way to display a zero count from the query or suggest a
better way of doing what I am trying to achieve.

TIA.

Zippy

 

[Jeff Boyce]

Have you looked into using a Totals query?

If you open a new query in design view, add the table and the two fields,
then click on the 'sigma' button (totals), you get a new row in the grid.
Keep the "GroupBy" for the TestType, and change it to "Count" for the
Result.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Zippy]

That's what I've used to get the counts I have got. The problem is when I
have all three of the counts in one query if any of them is equal to zero
then none of the counts is displayed (which is my problem at the moment).
I was trying to keep them in one query to make it easier to display on the
form I am using.

Zippy

 

[SteveM]

Are you sure there are no Nulls creeping in anywhere?

What is your SQL?

Steve

 

[Zippy]

Cheers Steve,

That got me going in the right direction and its working fine now.

Zippy