Altering field values

[diesel_medic]

Hi
I'm trying to produce a database that will convert Ebay download files to a format more suitable for import into Sage Line 50. I have a table "Customers" that has all the customer information in it.

One of the fields is "Country", this lists the full name of the country the customer lives in. However, Sage requires countries to be listed as a two letter abbreviated identifier, GB, US etc.. I have another table "CountryCodes", with two fields, the countries full name and its abbreviated identifier. I need to be able to compare every "Country" field in the "Customers" table with with the "Country" field in the in the "CountryCode" table and change it's value accordingly. I also need the "Country" field to change to fixed value (ZZ) if the country is not found in the "CountryCodes" table.

Any help would be greatfully recieved.

 

[KeithG]

something like below should work if you put your field name in there

Update Customers Left Join CountryCodes On customers.Country=CountryCodes.country Set Customers.Coutry=CountryCode.[AbbreviatedCountryField] Where CountryCodes.Country Is Null