Populate a field based on contents of another field

[Shirley]

I have a table for addresses from all over the world. I frequently create new records by importing the following fields from Excel: Address1, Address2, City, Prov/State, PostCode, Country.

I have a calculation field for sorting by postage requirement: CountryCode (Canada=1, US=2, Default=3). The Default is International postage.

Most of the Prov/State fields contain either the verbose form or an unofficial abbreviation. I want to create a new field “AbbrProv/State” for the official abbreviated form to use on mailing labels.

My question is how do I efficiently populate this field? Should I use a new linked table that contains all known forms of the Prov/State, a Set Field script, or do I make it a Calculation field? At the moment, I am primarily concerned with Canadian and American addresses but International ones may be added later.

[David McQueen]

If you have access to one of the phone number databases you may want to try making a related table of known wrong spellings. I had to do this once and had Canada Pro Phone and there were a lot of mistakes in the town names. I was able to use that to gather information.

HTH

Dave McQueen