How to count just 'Yes' in a field

[Gkirkup]

I have a query which counts the number of records between certain dates in a table, and of course use 'Count' for that. Now how do I count the records which have a 'Yes/No' field set to 'Yes'? I have several of these fields so I don't want to limit the query to return only 'Yes' fields, I want to count all of them.

Robert

 

[ajetrumpet]

You need to be more specific with this detail Robert..

e.g. - COUNT WHAT? ANY GROUPINGS? HOW MANY FIELDS? ANY AND ALL OF THEM? WHICH ONES IF ANY? DO YOU WANT 2 COUNT FIELDS, OR JUST ONE? ETC...

 

[CameronM]

Add an IIF() statement to query and set the value to 1 if the field contains Yes. Then you can sum the field to get the number of yes'.

In the example below tblYes contains a field Question1, which is of Yes/No datatype.

SELECT Count(tblYes.ID) AS CountOfID, Sum(IIf([Question1]=-1,1,0)) AS Q1Yes
FROM tblYes;

You can easily add more yes/no fields to the query without them limiting the number of records being returned.

 

[Gkirkup]

Adam: My query works but I am adding a count of three yes/no fields in the table. What I need to return is number of records, number of times in those records on which a 'late' yes/no field is set to 'Yes', number of times in which an 'order canceled' field is set to 'Yes', etc.
My feeling is that I probably need a sub-query for each of these, and then accumulate the results into a final query.

Robert

 

[CameronM]

Robert,

You actually want the Sum of these fields, not the Count. If you look at my previous post, you will see that Count(ID) would give you the total number of records, whereas SUM(IIF([Fieldname]=-1,1,0)) would give just the sum of values in the field - in this case every Yes is assigned a numeric value of 1.

 

[ajetrumpet]

Girk,

Cameron's idea is fine, if you just need the counts of YES's. But, he did forget the GROUPING that has to happen.

In addition, if you need the SPECIFICS of the counts in each field for the records, you COUNT every field. Here is an example to get you started:

SELECT COUNT(table.ID) As [Number of Records],
   DSUM("LATE", "TABLE", "[late] = -1") As [Number of Lates],
      DSUM("Orders Cancelled", TABLE, "[Orders Cancelled] = -1") As [Number of Orders Cancelled)
         FROM TABLE

Try that and see what happens. I forget as to weather or not you need groupings with DSUM's. If you do, you'll know rite away!


But at any rate, that is what you need...I'm almost sure of it.

 

[CameronM]

Cameron's idea is fine, if you just need the counts of YES's.

And if you look at the original question, that is exactly what is requested.

Now how do I count the records which have a 'Yes/No' field set to 'Yes'?

Also, Gkirkup does not state that there are any groupings - just that the count of records should be between two dates. Which would leave us with this query

SELECT Count(tblYes.ID) AS CountOfID, Sum(IIf([Question1]=-1,1,0)) AS Q1Yes
FROM tblYes
WHERE (((tblYes.QDate) Between #2/1/2009# And #2/28/2009#));