Link to home
Start Free TrialLog in
Avatar of Robert Logan
Robert LoganFlag for United States of America

asked on

Update query using REPLACE or SWITCH?

I have an Access 2003 database for university student information with several fields.  One of the fields is called MontanaCounty in the table, tblStudent.  I've imported several hundred student records into the database.  

These new records have numbers in the MontanaCounty field rather than the corresponding county names, e.g. the number, "1" instead of  "Butte Silver Bow," or the number "2" instead of "Cascade."  

I'm trying to write an Update Query that will replace all 56 county numbers (1 - 56) with the corresponding county names  So, I would need to replace --

"1" with "Butte Silver Bow"
"2" with "Cascade"
"3" with "Yellowstone"
...
"56" with "Lincoln"

So far, my Update Query (as follows) doesn't work.  

UPDATE tblStudent
SET MontanaCounty = replace (MontanaCounty, '1', 'Butte Silver Box') WHERE MontanaCounty = "1"
SET MontanaCounty = replace (MontanaCounty, '2', 'Cascade') WHERE MontanaCounty = "2"
SET MontanaCounty = replace (MontanaCounty, '3', 'Yellowstone') WHERE MontanaCounty = "3";

Does Access support an equivalent of a Case Select option?  Or, would a Switch function work and what would it look like?

Thanks, Experts!

SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Logan

ASKER

OK, I'm probably an idiot...but I DO have a separate table of county names and numbers -- it's called tblMontanaCounty.  It has three fields, intMontanaCounty (primary index), MountanaCountyNumber, MontanaCountyName.  Is there a simple way to update the field MontanaCounty (which has a county number) in tblStudent with the corresponding county name from tblMontanaCounty?  
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent help.  Thanks!!!