Count Problem

[Dawn]

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

 

[nata]

Try this:
SELECT Count(x.minus) AS field1, Sum(x.minus) AS field2
FROM x
WHERE (((x.minus)>0));

 

[John Spencer]

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]

Thanks for your help.

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