Summing one field based on the value of another field 1

[annsolomon]

I want to track the balance due on our health claims. I have a TransAmt field and a TransType field (e.g., "Approved", "Denied", "Pended", "Adjusted".

I only want to consider TransAmt if the TransType is "Approved" or "Adjusted"; Claims that are pended or denied still leave the full balance open so I want to ignore the TransAmt field when the TransType is "Pended" or "Denied".

Can someone help me with putting this query together. I'm tripping over my feet trying to use the IIf function.

Ann

 

[PHV]

SELECT ...
, Sum(IIf(TransType='Approved' OR TransType='Adjusted', TransAmt, 0))
...

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

 

[annsolomon]

Boy, do I feel silly. I forgot all about using OR. I've been out of the look for almost two years and it's amazing how much I've forgotten. Thanks for your help.

Ann

 

[lespaul]

you can also use IN instead of a string of ORs (not sure it works in an IIF statement though).

Sum(IIf(TransType IN ('Approved', 'Adjusted'), TransAmt, 0))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design