Update Query Options/Direction

[bigroo]

Hi All,

Not too sure which direction to go with this Update Query.
I import into my database data from a website based in Mexico, it is to do with AM and FM Mexican stations. I am trying to keep my information current, looking for new stations and changes to call letters.
The state abbreviations they use, I want to change this within my database due to the fact this data goes into another database which has different State abbreviations, mainly the two letter code instead of the 3 to 4 letters that is used within the imported data.



I have an update query setup to change this manually, but I don't think setting up 32 update queries is the best direction due to each Mexican State, I would like to do this in one press of a button on are form if possible.
Can I do this with VBA, are Macro or other suggestions please!!

Here is my update query at present, manual process.

UPDATE MexicanData SET MexicanData.State = "AG"
WHERE (((MexicanData.State)="Ags."));

Thanks

[June7]

Do you have a table of the corresponding abbreviations? Include that table in the query by joining on the respective 3/4-letter fields. This will make the corresponding 2-letter abbreviation available for the Update To, something like:

SET Mexican.State = CorrespondingTable.TwoLetterState

This does require that the same 3/4-letter abbreviations are always used in the imported data.

[bigroo]

Thanks June7!!

Yes, I do have a table with the desired Mexican State codes that I can use.
So, if I follow you correctly, I will need another column with the corresponding imported state codes with the desired state code and perform my updates from this point...correct?

[June7]

Yes, you seem to have understanding.

[bigroo]

June7,

Thanks, your solution works great!!
I don't know why I did not think of this myself, I guess looking at the issue too long!!