count DISTINCT [field1] where [field2]=True (1 Viewer)

[CoffeeGuru]

I am trying to get a distinct count of [FAMILY] where [ACTIVE] = True

I am using SQL server back end with pass through query to create my report. This is I guess important as DISTINCT becomes a valid operator...I think.

 

[pr2-eugin]

Why not GROUP BY?

SELECT Count([FAMILY]) As CountOfFamilies
FROM yourTableName
GROUP BY [FAMILY]
WHERE [ACTIVE] = -1;

 

[CoffeeGuru]

Sorry my bad

This is in a report footer

 

[pr2-eugin]

Huh??!!?? Could the Report not have a recordsource? Which could be this Query?