Change value of field dependent on what original field value is

[spudracer]

I'm building a query (if you saw my other thread, this is not for that database, but one I need to track a program).

I've got a file in Excel format that I need to import roughly once a month. In this file, there is a field titled Pay Code in a A, B, C format. What I'm wanting to do, is I'm creating a merged column in the query that will merge the Pay Code with the persons skill code, but to try and simplify it so I'm not always going the Find and Replace route.

I've already gone through and done the Find and Replace and changed everything to how I want it for now, but I'd like to prevent this in the future.

Is there a way, probably not in a query, to automate the Find and Replace function so that if I have the values I want replaced and the values to replace the original value already established in a table I can just run a macro or something to change the fields?

Best example I can provide:

Skill Code | Pay Code
A | A

After Find/Replace function with A being converted to 1

Skill Code | Pay Code
A | 1

Merged in query
A1

 

[FoFa]

Simplest is to use a lookup table with the original values, and the new values. Join the input to able to the lookup table, and use the new values when you do whatever,

OrigSC OrigPC NewSC NewPC
....A........A.......A.........1...

So joined on Skill Code and Paycode would resolve AA being an A1

Just as one way

 

[khawar]

you can use sql as below

update yourtable set yourtable.paycode=1 where yourtable.paycode="A"

 

[spudracer]

Thank you FoFa!!

Khawar, it's not a SQL database.

 

[khawar]

this is sql for use in query paste this sql in sql view of query and run the query your records will be updated