Aggregate count function

  • Thread starter Chris Motherwell via AccessMonster.com
  • 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
 
J

John Spencer (MVP)

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?
 
C

Chris Motherwell via AccessMonster.com

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
 
J

John Spencer (MVP)

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
 
C

Chris Motherwell via AccessMonster.com

I had to nest the queries to get it to work - the Sum and Iif combined did the trick! Thanks a lot

Chris
 
Top