Thanks I tried that earlier and I must have had the syntax wrong and gave up on it.
Thanks Again
Gaylen
>Gaylen,
>
>Add this as a field to your query:
>
>sum(iif(losses > 500000, 1, 0)) as big_loss_cnt
>
>HTH,
>Chad
>
>>I have a table with policyNo,FundYr,Incurred as fields.
>>
>>I can get the count and sum of the incurred(losses) by policy and FundYr but I want to know how to get the count of losses over 500,000 in the same sql statement.
>>
>>
>>select policyNo,FundYr,sum(incurred)as incurred,count(*)as total_cnt from claims group by policyNo,FundYr ..........................
>>
>>
>>
>>In the past I created a second cursor for over 500000 incurred losses and then joined it to the above cursor. I tried a number of IIF variations but to no success.
>>
>>I know I am throwing up a soft ball and someone will hit it out of the park probably in record time :-).
>>
>>Thanks
>>Gaylen