Separate sums from a single field?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LongWayFromHome - 29 Sep 2006 17:00 GMT

An imported table lists each account's credits (>0) and debits (<0) in the
same field. Each account has multiple records, some showing a positive
number, others a negative. I am trying to use a Make-Table query to derive
two sums for each account, which are then placed in another table with TWO
fields for each account, one showing the negative debit and the other showing
the positive credit. Can I do this with one query, or must I use two?

Thanks!

Signature

Dave

Reply to this Message

John Spencer - 29 Sep 2006 18:34 GMT

SELECT AccountNumber
, Sum(IIF [Amount]>0,Amount,Null) as Credit
, Sum(IIF [Amount]<0,Amount,Null) as Debit
FROM SomeTable

If you want to get rid of the negative, the wrap the calculation in the Abs
function

, Abs(Sum(IIF [Amount]<0,Amount,Null)) as Debit

> An imported table lists each account's credits (>0) and debits (<0) in the
> same field. Each account has multiple records, some showing a positive
[quoted text clipped - 6 lines]
>
> Thanks!

Reply to this Message

Klatuu - 29 Sep 2006 18:41 GMT

You can do it with one. You just need two calculated fields.

Credits: Iif([SourceTran] >0, [SourceTran], 0)
Debits: Iif([SourceTran] <0, [SourceTran], 0)

> An imported table lists each account's credits (>0) and debits (<0) in the
> same field. Each account has multiple records, some showing a positive
[quoted text clipped - 4 lines]
>
> Thanks!

Reply to this Message