multiple sum on same field with criteria

[starbird2273]

ok, I have seen several great ideas that get me part way to my need, but nothing that seems to get me what I am looking for.

Here are the fields I am using

State
Vendor
Product
amt_Identified
amt_Closed
amt_Recovered
amt_Balance
Recovery_Reason

I want my output to look like this, grouped by Vendor, Product, State, Sum on the others.

Vendor
Product
State
amt_Identified
amt_Closed
amt_Recovered
amt_Balance
[b]amt_Recovered [i]for Recovery_Reason that begins with DOA[/i][/b]

I can't seem to get the part in bold to work. it is the same field I want to calc on, but only if the reason fld is something that begins with DOA

any help would be appreciated.

 

[cranebill]

sum(iif (Left(amt_Recovered,3) = 'DOA',1,0))

 

[starbird2273]

Thank you. I am a bit confused. would my complete SQL help?

 

[cranebill]

yes

 

[starbird2273]

SELECT dbo_V_rpt_Claim.DTL_Recovering_Organization_Name, 
IIf([Bus_Line_Cd] Is Null,"Commercial",
IIf([Bus_Line_Cd]="COM","Commercial",
IIf([Bus_Line_Cd]="Commercial","Commercial",
IIf([Bus_Line_Cd]="MCR","Medicare",
IIf([Bus_Line_Cd]="Medicare","Medicare","Medicaid"))))) 
AS Product, dbo_V_rpt_Claim.CLM_Site_Cd_State, 
Sum(dbo_V_rpt_Claim.FIN_Audit_Amount) AS 
SumOfFIN_Audit_Amount, 
Sum(dbo_V_rpt_Claim.FIN_Gross_WriteOff_Amount) AS 
SumOfFIN_Gross_WriteOff_Amount, 
Sum(dbo_V_rpt_Claim.FIN_Gross_Recovery_Amount) AS 
SumOfFIN_Gross_Recovery_Amount, 
Sum(dbo_V_rpt_Claim.FIN_Recovery_Balance_Amount) AS 
SumOfFIN_Recovery_Balance_Amount, 
Sum([dbo_V_rpt_Claim]![FIN_Gross_Recovery_Amount]) AS DOA
FROM dbo_V_rpt_Claim
WHERE 
(((dbo_V_rpt_Claim.DTL_Recovering_Organization_Code)="ALI" 
Or (dbo_V_rpt_Claim.DTL_Recovering_Organization_Code)="JRP" 
Or 
(dbo_V_rpt_Claim.DTL_Recovering_Organization_Code)="CTK"))
GROUP BY dbo_V_rpt_Claim.DTL_Recovering_Organization_Name, 
IIf([Bus_Line_Cd] Is Null,"Commercial",
IIf([Bus_Line_Cd]="COM","Commercial",
IIf([Bus_Line_Cd]="Commercial","Commercial",
IIf([Bus_Line_Cd]="MCR","Medicare",
IIf([Bus_Line_Cd]="Medicare","Medicare","Medicaid"))))), dbo_V_rpt_Claim.CLM_Site_Cd_State
ORDER BY dbo_V_rpt_Claim.DTL_Recovering_Organization_Name, 
IIf([Bus_Line_Cd] Is Null,"Commercial",
IIf([Bus_Line_Cd]="COM","Commercial",
IIf([Bus_Line_Cd]="Commercial","Commercial",
IIf([Bus_Line_Cd]="MCR","Medicare",
IIf([Bus_Line_Cd]="Medicare","Medicare","Medicaid"))))), 
dbo_V_rpt_Claim.CLM_Site_Cd_State;

The line I have as DOA now

Sum([dbo_V_rpt_Claim]![FIN_Gross_Recovery_Amount]) AS DOA

is what I want a sum of if dbo_V_rpt_Claim.DTL_Action_Process_Code_Short_Description starts with DOA (there are 5 codes that start DOA)

Thanks!

 

[cranebill]

so if "DTL_Action_Process_Code_Short_Description" first three letters is "DOA" you want the sum of "amt_Recovered".

If so then:

sum(iif (Left(DTL_Action_Process_Code_Short_Description,3) = 'DOA',amt_Recovered,0))

 

[starbird2273]

Running it now. will let you know the results. thanks for your help!

 

[starbird2273]

Worked like a charm, thank you!