By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,567 Members | 1,554 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,567 IT Pros & Developers. It's quick & easy.

Update query with table values in a range

P: 20
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.
Attached Files
File Type: xlsx Example.xlsx (10.2 KB, 387 views)
Apr 2 '14 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 10K+
P: 12,430
Just import that excel sheet into Access and join to it on the 3 digit value to get the 2 digit value.
Apr 3 '14 #2

P: 20

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.
Apr 3 '14 #3

Expert Mod 10K+
P: 12,430
I assume you have something like this:

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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Code2
  2. FROM
  3.    Table1 INNER JOIN
  4.    Table2 ON Table1.Code3 = Table2.Code3
Apr 3 '14 #4

Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Table1]
  2.        INNER JOIN
  3.        [Table2]
  4.     ON Table1.Code3=Table2.Code3
  5. 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?
Apr 3 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.