Separate sums from a single field?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
You can do it with one. You just need two calculated fields.

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