Re: Count Problem
- From: John Spencer <spencer@xxxxxxxxx>
- Date: Tue, 10 Jun 2008 07:18:14 -0400
Count counts the existence of a value. Since x.Minus>0 is always going to return a value of true (-1) or False (0) (except in the rare instance the Minus is a null value) you are going to get a count = to the number of records.
Either one of the following expressions should return the count you want
Abs(Sum(x.Minus>0))
Count(IIF(x.Minus>0,X.Minus,Null))
The first works by adding up all the -1 and 0 returned by the expression x.Minus > 0 and then using Abs to strip off the negative sign.
The second works since the IIF returns Null (null aren't counted) or the value of X.minus (non-null values are counted).
Hope this helps
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Dawn wrote:
Table x has only one field “minus”, whose value from (-∞,+∞)..
Query as below:
Select count(x.minus>0) as field1,
Sum(iif(x.minus>0,minus,0)) as field2
From x;
But after running, the result of field2 is correct, while field1 is the total records number of table x, not what I wanted.
As One of the MVP recommended in a similar problem, he used function: “abs”,
Query:
Select abs(x.minus>0) as field1,
Sum(iif(x.minus>0,minus,0)) as field2
From x;
When running , it occurs run-time error.
What I want is to calculate the sum of minus which is over zero, and the respective records number.
Thanks.
Dawn
- Follow-Ups:
- Re: Count Problem
- From: Dawn
- Re: Count Problem
- Prev by Date: Re: Update query based on previous record
- Next by Date: Re: Unwanted alphanumeric sort on time, incorrect min/max value
- Previous by thread: access query to find unique values
- Next by thread: Re: Count Problem
- Index(es):
Relevant Pages
|
