SQL using IIF expressions and different criteria returns same amounts (1 Viewer)

[April15Hater]

What I am trying to do is list all of the TRecsaccount data and the corresponding counts and sums of debits and credits separated out into columns. Each record is notated DR or CR in the DRCR field for Debits and Credits respectively.

What I did was made a query grouped by TRecsaccount, and made an expression using a conditional to bifurcate the DR's and CR's into seperate fields and then summed and counted in 2 separate fields accordingly. The problem is that the amounts in DR and CR should be different, but they are not.

SELECT tblAcctTranslation.TRECSAccount, 
  IIf("DRCR='DR'",Sum([Amount]),0) AS DRAmt, 
  IIf("DRCR='CR'",Sum([Amount]),0) AS CRAmt,  
  IIf("DRCR='DR'",Count([Amount]),0) AS DRCount, 
  IIf("DRCR='CR'",Count([Amount]),0) AS CRCount
FROM tblAcctTranslation INNER JOIN TLP_Outstanding ON tblAcctTranslation.ImportAccountwName = TLP_Outstanding.ImportAccount
GROUP BY tblAcctTranslation.TRECSAccount
ORDER BY tblAcctTranslation.TRECSAccount;

 

[vbaInet]

You were almost there, you just nested the wrong way round. So an example would be:

Sum(IIf([DRCR]='DR',[Amount],Null)) AS DRAmt

Also note the Null part. It just makes it slightly more effecient.