Update query with table values in a range

[GirthJohnson]

I'm using MS Access 2007 and I have a table that has a field wtih 188 unique values (3 digit). I want to convert them to one of 22 unique values (2 digit).

I have an excel spread sheet of what each unique 3 digit value should map too and there's really no rhyme or reason to it so I can't specify ranges.

Please see the attached example.

Not sure if I Should use the dlookup function or create an update query with with 188 diferent criterion. I'd like to utilize a query so I can add it to my current access db project though I'm not opposed to using VBA if its better/easier.

[Rabbit]

Just import that excel sheet into Access and join to it on the 3 digit value to get the 2 digit value.

[GirthJohnson]

Rabbit,

Maybe I'm making this harder than it is but, don't I have to map each individual 3 digit value to the specific 2 digit value? I don't understand how a simple join would convert values correctly.

The long drawn out way would be to make 22 update queries that update specific 3 digit values to their respective 2 digit values.

[Rabbit]

I assume you have something like this:

Table1
Code3 - CHAR(3)

Table2 (imported from excel)
Code3 - CHAR(3)
Code2 - CHAR(2)

Then this join returns the Code2 from the first table's Code3.

SELECT Code2
FROM
   Table1 INNER JOIN
   Table2 ON Table1.Code3 = Table2.Code3

[NeoPa]

The join gives you access to the related values. Consider the scenario painted by Rabbit but assume your original data in [Table1] also has a field called [Code2] added which starts with nothing in it. A simple update query could be set up to apply the desired values (held in [Table2]) to the new field in your original data :

UPDATE [Table1]
       INNER JOIN
       [Table2]
    ON Table1.Code3=Table2.Code3
SET    Table1.Code2=Table2.Code2

SQL like this would go through every record of [Table1] and take the matching record from [Table2]. It then, for each record, adds the [Code2] value from [Table2] into the [Code2] field of [Table1]. Does that make sense?