Counting records with Values

  • Thread starter Thread starter wonderin32
  • Start date Start date
W

wonderin32

I'm basically a beginner in Access, but trying to become more proficient.
Generally I pull data into Excel to manipulate, but I've run up against too
big a database.

I'm trying to count all of the records that have a value of greater than 0.

I'm pulling from another query that has multiple records. The details are:
Multiple months of sales information (this is where I'm trying to count >0).
Multiple franchise locations and multiple SKUs.

I'm trying to pull how many franchise locations Sales of >0 units for each
SKU.

I'm good with the SELECT piece, but COUNT is just counting all of the
records. I tried COUNT(IIF(X)>0), but that didn't seem to work either.
Everything was coming up 1's.

Any ideas?
 
Try

Sum(IIf(Nz(X,0)>0, 1, 0))

The Nz function will handle the case where X is actually a Null value: it'll
substitute 0 for Null. You're then checking to see whether that's greater
than 0 or not: if it is greater than 0, the Iif function returns 1,
otherwise it returns 0. You then sum the all of the 1s to get the count.

Another alternative could be to use Count("*"), and have a criteria of > 0
for the Where clause.
 
How about

Count(IIF([SomeField]>0,1,Null)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
SELECT COUNT(*) FROM tableName WHERE sales > 0


but that would works if you only count one field. Use the iif( ) if you
have to count on multiple fields, in the same query:


SELECT iif( COUNT(QtyReceived >0, 1, null)), iif( COUNT( QtySold > 0, 1,
null )) FROM tableName



Vanderghast, Access MVP
 
Well, you'd need an additional closing parenthesis (<g>), but sure, that
would work too.

In fact, given that Count only counts non-null values, realistically you
could put any value you wanted in the True part of the IIf statement.

And I noticed a typo in my answer regarding the Count function. You wouldn't
put quotes around the asterisk:

SELECT Count(*)
FROM MyTable
WHERE X > 0

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John Spencer said:
How about

Count(IIF([SomeField]>0,1,Null)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Douglas J. Steele said:
Try

Sum(IIf(Nz(X,0)>0, 1, 0))

The Nz function will handle the case where X is actually a Null value:
it'll substitute 0 for Null. You're then checking to see whether that's
greater than 0 or not: if it is greater than 0, the Iif function returns
1, otherwise it returns 0. You then sum the all of the 1s to get the
count.

Another alternative could be to use Count("*"), and have a criteria of >
0 for the Where clause.
Click to expand...
Click to expand...
 
Back
Top