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.

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