IIF Statement Trouble

[skumar14]

Hello,

I was hoping someone could help me with this problem or let me know if it isn't possible. I have three fields Contract Number, Sustainable, and Total Amount. The sustainable field is a flag with 0 or 1. In my data set i keep on getting duplicates for the contract number because in some instances the flag is 0 and others its 1. I would like to create a new field "Final sustainable field" with the IIF statement that says if the contract number has the flag of 0 and 1 then make it 1 and leave all the other values as is. Is this possible, any help would be appreciated.

Thanks!

 

[skumar14]

Hey Joe i just realized because since this is part of a series of queries that i am using to build a report I could actually take the max value in one of my earlier temp tables and i get the correct results. However I wouldn't have thought about that without your help, I'm sorry if i wasted your time but thanks for all your help it pushed me in the right direction!

 

[Joe4]

You are welcome. Glad you got it figured out.