Count record as 1 when qty is not zero

[accessfever]

Hi all,

I have a database table which holds part number, its usage qty and some other stuffs. I wanted to create a selection query to have a new column to count how many parts which do have a qty <> 0.
For examples, if there are 3 records having usage qty <> 0 in the table of 5 records, then I want the query to return a count of 3 not 5 in the count column.

Does it a way to do that in a query?

 

[Beetle]

Something like;

Select Count(YourTable.PartQty) As CountOfPartQty
From YourTable
Where YourTable.PartQty<>0

 

[accessfever]

Your statement worked well. But now people wanted to have a report to show (a) the count of how many items have zero qty and (b) the count of how many items have qty. It seems not working if I removed the criteria of "qty <>0".

Any suggestion?

 

[Beetle]

If you want to have these in the same query then use the DCount function in the field row of the query, for example -

in one column put;

PartsWithQty: DCount("*", "Yourtable", "PartQty <> 0")

in another column put;

PartsWithZero: DCount("*", "YourTable", "PartQty = 0")

 

[plog]

SELECT Sum(IIf([qty]<>0,1,0)) AS TotalNonZero, Sum(IIf([qty]=0,1,0)) AS TotalZero
FROM YourTableName;

 

[accessfever]

Both worked well. Thanks very much!

 

[Galaxiom]

Plog's query will be more efficient than using the DCounts.