IIF statement with Aggregate function

[Chiradeep]

I have MS Access table with:

Loan ID Text
Origination Date Date/Time
Outstanding Principal Number
Charge-Off Amount Number
Default Date Date/Time
Loan Status Text

Status is mutually exclusive and can have values like Default, Charge Off etc. I need to have separate summation of [Outstanding Principal] if status = Default, and [Charge-Off Amount] if status = Charge-Off; for every Origination Qtr and Default Month combination.

SELECT Format$([Origination Date],"yyyy-q") AS [Orig Qtr], Format$([Default Date],"yyyy-mm" AS [Def Mon], IIF([Loan Status]="Default", Sum([Outstanding Principal]),0), IIF([Loan Status]="Charge-Off", Sum([Charge Off Amount]),0)
FROM <table name>
WHERE [Loan Status]="DEFAULT" OR [Loan Status]="Charge-Off"
GROUP BY Format$([Origination Date],"yyyy-q"), Format$([Default Date],"yyyy-mm"),
IIF([Loan Status]="Default", Sum([Outstanding Principal]),0),
IIF([Loan Status]="Charge-Off",Sum([Charge Off Amount]),0);

The above doesn't work b'coz: "Cannot have Aggregate function in Group By..". If I take out the last 2 columns from Group By then also it doesn't work with "tried to execute query that does not include the iif expression as part of aggregate function".

Can someone please help?

 

[PHV]

Have you tried to Sum the Iif ?
SELECT Format$([Origination Date],"yyyy-q") AS [Orig Qtr], Format$([Default Date],"yyyy-mm" AS [Def Mon], Sum(IIF([Loan Status]="Default", [Outstanding Principal],0)), Sum(IIF([Loan Status]="Charge-Off", [Charge Off Amount],0))
FROM <table name>
WHERE [Loan Status]="DEFAULT" OR [Loan Status]="Charge-Off"
GROUP BY Format$([Origination Date],"yyyy-q"), Format$([Default Date],"yyyy-mm");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Chiradeep]

Cool... That worked.