Aggregate count function

  • Thread starter Thread starter Chris Motherwell via AccessMonster.com
  • Start date Start date
C

Chris Motherwell via AccessMonster.com

I've created a query that counts the number of records based on a criteria of Yes or No (for a Yes/No data type). Is there any way to have the results display a zero (or something) when there are no Yeses or Nos?

CM
 
I guess I am missing something here. How can you have zero yes and zero no in a
recordset? A yes/no field has to have one value or the other.

Are you applying some criteria and therefore returning zero records in the recordset?
 
Thanks for the reply John.
Let's say the criteria is Yes for a Yes/No field. If there are no Yeses in the the data, is it possible to display a zero to show this result?

CM
 
Can you post the query you are using? Or are you using one of the VBA aggregate
functions? A very basic query might look like:

SELECT Sum(IIF(SomeYN=True,1,0)) as CountYes,
Sum(IIF(SomeYN=False,1,0)) as CountNo,
Sum(IIF(SomeYN2=True,1,0)) as CountYes2,
Sum(IIF(SomeYN2=False,1,0)) as CountNo2,
FROM SomeTable
 
I had to nest the queries to get it to work - the Sum and Iif combined did the trick! Thanks a lot

Chris
 
Back
Top