Separate sums from a single field?

[Guest]

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!

 

[John Spencer]

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

 

[Guest]

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

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