I need to create a column based on the value in another field. 1

[bmoberly8888]

I have a field - PrsTypCode and the values of that field are B1,B2,B3,E1,E2,E3,T1,T2,T3. I want to create a field called "Cost" and if PrsTypCode = B1 or E1 or T1 - I want a value of 75, and if PrsTypeCode = B2 or B3 or E2 or E3 or T2 or T3, I want a value of 40.

 

[lespaul]

you'll need to use a function like this in your update query:

...iif(PrsTypCode IN ("B1", "E1", "T1"), 75, 40)


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

 

[bmoberly8888]

That's great! - but I just found out I need 3 values - 82, 75 or 40 - so if it's B1 or E1 or T1 the value needs to be 82, and if it's B2 or B3 or E2 or E3 or T2 or T3, it needs to be 75, and if it's B6 or E6 or T6, it needs to be 40.

 

[lespaul]

then you can use a nested if:


iif(PrsTypCode IN ("B1", "E1", "T1"), 82, iif(PrsTypCode IN ("B2", "B3", "E2", "E3", "T2". "T3"), 75, 40)