update imported records with different criteria

[Dani2009]

I need to import a file that has a district name as a field. The district
names are different and I need to update them to the correct name, for
example in the file I will import the district name apears as "Alhambra USD"
and I would like to update it to "Alhambra Unified School District". There
are several district names that need to be updated. Do I need to create an
update query for every district name I need to update or how can I create a
single update query that will update multiple criteria to multiple values?

 

[Arvin Meyer [MVP]]

You can write some VBA function code with a Select Case statement and use
the function in an Access update query. Unless you have do this more than
once, I'd suggest just running separate queries, or actually just changing
the criteria and update value for each new set.

 

[John Spencer]

One method is to build a translation table with two columns and use that to
update. Column one - CorrectName; Column two - Alternative Name

Add one record for each alternative along with the correct (or desired) name.

Then your update query might look like

UPDATE TargetTable INNER JOIN TranslationTable
On TargetTable.DistrictName = TranslationTable.AlternativeName
SET TargetTable.DistrictName = [TranslationTable].[CorrectName]

This is good technique if you must do this frequently and if the list of
alternative names tends to change over time. For instance Alhambra USD
becomes listed as AUSD for some reason. Then you just add one more record to
the translation table and this value will also get corrected.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dani2009]

Thank you so much. That worked perfectly.

One method is to build a translation table with two columns and use that to
update. Column one - CorrectName; Column two - Alternative Name

Add one record for each alternative along with the correct (or desired) name.

Then your update query might look like

UPDATE TargetTable INNER JOIN TranslationTable
On TargetTable.DistrictName = TranslationTable.AlternativeName
SET TargetTable.DistrictName = [TranslationTable].[CorrectName]

This is good technique if you must do this frequently and if the list of
alternative names tends to change over time. For instance Alhambra USD
becomes listed as AUSD for some reason. Then you just add one more record to
the translation table and this value will also get corrected.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I need to import a file that has a district name as a field. The district
names are different and I need to update them to the correct name, for
example in the file I will import the district name apears as "Alhambra USD"
and I would like to update it to "Alhambra Unified School District". There
are several district names that need to be updated. Do I need to create an
update query for every district name I need to update or how can I create a
single update query that will update multiple criteria to multiple values?