Jet SQL translation query syntax?

[AnotherJob]

I want to translate string fields in an MS Access table, mapping them to other strings. Now I do that in a VBA loop, iterating through the table and calling a VBA function to map the new values, But I suspect there may be a way to do it just in SQL. Is there?

Suppose I have two tables, like this:

Table1 (translantion table)
SOURCE                TARGET

Massachusetts         MA
Vermont               VT
New Hampshire         NH
New York              NY


Table2 (data table)
CITY                  STATE                    

Arlington             Massachusetts         
Portsmouth            New Hampshire
Portland              Maine

Is there a Jet-SQL query that would update Table1, remapping the STATE values in Table2? In practice my translation table might have thousands of values. Thank you in advance.

 

[lespaul]

query that would update Table1, remapping the STATE values in Table2?

do you really mean to update Table1? I'm guessing you want to take what is in the STATE field in Table2 (the full state name) and replace it with the abbreviation (found in Table1 as TARGET)....is that not what you want to accomplish? If so something like (typed not tested....suggested: make copy before running an update!):

UPDATE Table2 INNER JOIN Table1 ON Table2.State = Table1.Source SET Table2.STATE = Table1.Target

Leslie

Have you met Hardy Heron?

 

[AnotherJob]

Thank you, yes I did mean that I want to update Table1, and your syntax does it correctly. I was trying the SET in the wrong place.