Sum of only positive numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karl - 28 Jun 2007 21:32 GMT

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.

Reply to this Message

Douglas J. Steele - 28 Jun 2007 22:34 GMT

SELECT Sum(MyField)
FROM MyTable
WHERE MyField > 0

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hello,
>
[quoted text clipped - 12 lines]
>
> thanks in advance.

Reply to this Message

Karl - 28 Jun 2007 22:58 GMT

Where do I put that information?

> SELECT Sum(MyField)
> FROM MyTable
[quoted text clipped - 16 lines]
> >
> > thanks in advance.

Reply to this Message

John Spencer - 29 Jun 2007 02:02 GMT

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
'====================================================

> Hello,
>
[quoted text clipped - 9 lines]
>
> thanks in advance.

Reply to this Message