Sum of only positive numbers

[Guest]

Hello,

I have a field that contains banked time by employees. So some days the
employees bank time and some days employees use banked time. So days where
bank time is gained the value is positive and days when it is used the value
is negative. I have a query that calculates the sum of banked time for a
month, however I would like to have a query that only sums the positive
values (I.e time banked). Currently I just have a query that uses the sum
funcition in the total row of the query to sum the banked hours of the month.
Is it possible to use this function but only find the sum of positive numbers
in the field.

thanks in advance.

 

[Douglas J. Steele]

SELECT Sum(MyField)
FROM MyTable
WHERE MyField > 0

 

[Guest]

Where do I put that information?

SELECT Sum(MyField)
FROM MyTable
WHERE MyField > 0

 

[John Spencer]

To sum only the positive hours you can use an expression like the
following.

Field: Sum(IIF([Time Banked]>0,[Time Banked], Null))

Or you can add the field to the query a second time, change the total to
"Where" and then enter > 0 as the criteria

Field: [Time Banked]
Total: Where
Criteria: > 0



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