"Translate" certain strings

[BDibz]

This seems like such a simple problem, but I can't think of an elegant way to do it.

I'm trying to salvage a dirty old database and feed its raw data into a new, cleaner one. I need to keep its input fields the same, since new data is regularly appended to it. However, I have a query I am building that pulls the necessary data from it and calculates the needed output fields that I can then use to build my new version of the database.

The main problem I have right now is that I need to translate old values into new ones; so every time the string "AF2" Is present, it should translate to "Glendale" instead, for example. Is there any simple way to do this?

[CJ_London]

create a lookup table with the old value and the new value

then in your query have a join from the old data to the old value in your lookup table and use instead the new value

[BDibz]

create a lookup table with the old value and the new value

then in your query have a join from the old data to the old value in your lookup table and use instead the new value

That did it. Thanks.