[thelist] Counting Nulls and Not-Nulls in Access

[Tab Alleman]

Anybody know how to do this in Access (Jet 4.0)?

Say I've got a table like,

IDENTITY	ProductID	DateSold

...there can be multiple occurances of each ProductID, and DateSold can
be NULL.

I want to get output like this:

ProductID	TotalCount	QuantitySold	QuantityAvailable

Can I do this in a single query?  My latest attempt was:

SELECT
	ProductID
,	Count(*) AS TotalCount
,	Count(ALL DateSold) AS QuantitySold
,	TotalCount - QuantitySold AS QuantityAvailable
FROM MyTable
GROUP BY ProductID

But it didn't like it:

Syntax error (missing operator) in query expression 'COUNT(ALL
DateSold)'.