Count based on Criteria

[joe789]

Hi Folks,

I have a table with about 20 fields in it, about a dozen of the fields are set as either 'Yes' or 'No'. I would like to somehow count the number of 'Yes' values in each one of the fields automatically. I have tried the count function, and all it does is show me the total number of records for each one of the fields that I list in the query. Is there anyway to tell access to only show 'Yes' counts for each one of the dozen or so fields. This would really help me, because it would be much faster than to have to run a query on each and every field to get the number of 'Yes' records for the field.

Any help would be greatly appreciated,

Thank you,

Joe

 

[beef]

Attached is a sample that may be what you are looking for


Cheers!!!!


Lou

 

[Jon K]

If the fields are of Data Type Yes/No (i.e. not text fields), you can do it with a query like this:-

SELECT Abs(Sum([Field1])) as [CountOfField1],
Abs(Sum([Field2])) as [CountOfField2],
Abs(Sum([Field3])) as [CountOfField3]
FROM [TableName];


If the fields are text fields, you can use:-

SELECT Sum(IIF([Field1]="Yes",1,0)) as [CountOfField1],
Sum(IIF([Field2]="Yes",1,0)) as [CountOfField2],
Sum(IIF([Field3]="Yes",1,0)) as [CountOfField3]
FROM [TableName];