Count help needed

[Leslie67]

I have a badly constructed database that I've inherited and don't really have time right now to rewrite the process the way it should be done. Bearing this in mind...

I have a table with a "Category" field, a "Target Met" field and a "Target Exceeded" field.

I have a query that counts all the "Yes" in the "Target Met" field and sorts them by category. I have the same query for the "Target Exceeded" field that counts "No".

My problem comes from the fact that sometimes there is no count for the "target exceeded" - that is the count is 0 since all the targets were met for a category. When this happens, that category doesn't show up in my results.

So the "Yes" query returns:

dsk 10
lap 7
sof 20
ids 20
prt 5

And the "no" query returns:

dsk 1
sof 2
ids 1
prt 1

I want to force a 0 into that category.

Any ideas?

 

[DBAMJA]

A simple way to take care of this would be to do a new query joining the two existing queries on the category field and make sure that the join is set to include all of the "yes" categories and only those that match "no" categories.

When you drop the target exceeded count into the query design manager, define the field as 'Target_Exceeded:IIf(IsNull([CountOfTarget Exceeded]),0,[CountOfTarget Exceeded])'. This will fill in a 0 where there is no value for the target exceeded.

Hope this helps.