Need Positive, Negative and Absolute Value Sum Totals

[nancier]

I have a table with 10,000 various negative and positive Amounts. I am trying to do a query where I get 1 positive Total, 1 Negative total and 1 Absolute Value Total. It seems I can only get one at a time to work. If I use a union query, I can't get it to work because I can't seem to use the Amt column more than once. Below query gives just the negative total. Any idea how to get all three at once?

SELECT Sum(tblAmount.Amt) AS SumOfAmt
FROM tblAmount
HAVING (((tblAmount.Amt)<0));

I would like the results to look like this

ABS........Positive........Negative
-----------------------------------
80,000.....100,000.........20,000

 

[r937]

select sum(Amt)              as ABS
     , sum(iif(Amt>0,Amt,0)) as Positive
     , sum(iif(Amt<0,Amt,0)) as Negative
  from tblAmount

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[nancier]

Thanks, works beautiful