CODEWITHSUNDEEP
sqlms-access
harryg
harryg

Reputation: 24107

SUMIF twice for the same field in an SQL query in MS Access

I have a table containing a list of "Money in Money Out" amounts along with dates and client IDs.

The MIMO field contains a positive number if the record is money in and a negative number if money out.

I want to perform a query that sums MIMO but to give the total money in and money out separately for all records between 2 dates. It does this by summing only if the amount is >0 for money in, and the vice-versa for money out.

I have this at the moment but I beleive my syntax is completely wrong:

SELECT Sum(MIMO.MIMO) AS SumOfMIMO HAVING (((Sum(MIMO.MIMO))>0), Sum(MIMO.MIMO) AS SumOfMIMO1 HAVING ((Sum(MIMO.MIMO))<0), MIMO.AccountNum
FROM MIMO
GROUP BY MIMO.TransactionDate, MIMO.AccountNum
WHERE ((MIMO.TransactionDate) Between #12/1/2012# And #12/31/2012#) AND ((MIMO.AccountNum)="12345"));

Upvotes: 1

Views: 2591

Answers (2)

iDevlop
iDevlop

Reputation: 25272

You could also calculate this with a Pivot: 

TRANSFORM Sum(Mimo.Mimo) AS Expr1
SELECT AccountNum, TransactionDate
FROM Mimo
WHERE Sgn([Mimo])<>0 And TransactionDate Between #12/1/2012# And #12/31/2012#
GROUP BY AccountNum, TransactionDate
PIVOT Sgn([Mimo]);

Upvotes: 1

Lamak
Lamak

Reputation: 70678

Try IIF:

SELECT  SUM(IIF(MIMO.MIMO >= 0,MIMO.MIMO,0)) AS MoneyIn,
        SUM(IIF(MIMO.MIMO < 0,MIMO.MIMO,0)) AS MoneyOut
FROM MIMO
WHERE ((MIMO.TransactionDate) Between #12/1/2012# And #12/31/2012#)) 
AND ((MIMO.AccountNum)="12345"));

Upvotes: 4

Related Questions