update multiple values in query

[pbuethe]

I want to link a field in one table to a corresponding field in another, but some of the possible values do not match. I want to change these 3 values in the first table to match their corresponding values in the 2nd table. e.g.

if field1 = "A" change it to "B"
if field1 = "C" change it to "D"
if field1 = "E" change it to "F"
all other values of field1 are unchanged.

Is there any way to do this in a single query (Design or SQL view)?

Thanks for your help.

 

[RichO]

Because you can't select a field more than once in an update query, I believe you would have to design a single update query, but run it in 3 passes changing the criteria and the "update to" value each time. It's a pretty quick and simple procedure and it sounds like a "one time" thing that you have to do.

 

[pbuethe]

Thanks Pat. Your suggestion worked.