Count - return a value of zero for a null count

[Alan Greiner]

I have a field in a table that contains one of six values
(I, II, III, IV, V, or SCR) I want to do a count for each
of these values but want to return a value of zero if
there are no occurances of one of the six values within a
given grouping.

How do I accomplish this?

Thanks for your help!

Alan

 

[jfp]

use a second table listing all possible values (you perhaps have this
already for referential integrity purposes). Use an outer join (left or
right, depending on how you set it up) over the two tables - select the
field in the table i suggest (the "lookup" table) and select count(*) in
your table (the "data" table)
-=-=