Edit Replace via Script Several Changes

[racer25]

Hi All,

I am looking for the smart way of doing this rather than the laborious.

Within my table 'Contacts' I have the field 'City' which as you would expect contains the city.

The data in the City field which is sourced from the web can often merge two regional municipalities into 1 and I know through digging in behind the data what the correct answer should be.

I am looking to accomplish a list of edit replaces that takes 30 + edits and automates.

Change "Cleveland\Akron" to "Cleveland"
Change "Dublin 2" to "Dublin"
Change "Greensboro/Winston-Salem" to "Greensboro"
etc. etc.

There are 10,000 plus entries in the table of which many are correct.

While I am somewhat familiar with VB I have not gone any where near data changes like this so have no 'good' idea where to start hence my reaching out.

Many thanks,

Rob

 

[madefemere]

Hello,

Create a table of Change T_Change_City with fields : CityWrong and CityChange where you put the wrongs and the corrections.

And you create an UpDate Query with the follow code :

UPDATE Contacts INNER JOIN T_Change_City ON Contacts.City = T_Change_City.CityWrong SET Contacts.City = [CityGood];

Have a good continuation

 

[racer25]

Many thanks madefemere - works a treat!