Count of yes

[Rbutts25]

Ok, I have gotten pretty far, just a few more bigs and I should be out of everyone's hair. I am trying to count the number of yes's from a Yes/No Format. I have gotten an sql, but it is bringng back the count of the total Yes/No not just the yes's. Does anyone have any advice, the sql is:


SELECT Count(([Active]=Yes)) AS [Count]
FROM tblMBUSAResourceList;


Ryan

 

[fuzzygeek]

There may be a better way, but at least this works:

SELECT Sum(IIf(tblMBUSAResourceList.Active=Yes,1,0)) AS Count
FROM tblMBUSAResourceList;

 

[Pat Hartman]

Here's two other simple solutions. They will each be slightly faster since no IIf() needs to be evaluated. If you ran the queries on a large enough recordsource, qry1 would probably be the fastest:

SELECT Abs(Sum([Active])) AS [Count]
FROM tblMBUSAResourceList;


SELECT Count(*) AS [Count]
FROM tblMBUSAResourceList
Where [Active]=Yes;

 

[Rbutts25]

Thank you very much for your help, the solution you gave me worked extremely well.


Ryan