[thelist] Counting Nulls and Not-Nulls in Access
Tab Alleman
Tab.Alleman at MetroGuide.com
Mon May 10 11:20:39 CDT 2004
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)'.
More information about the thelist
mailing list