Question Replacing Names with Update Query (1 Viewer)

[Kooshster]

This shouldn't be a tough problem, but for some reason I'm stumped. I've got a huge database which has city names in it. The names themselves are outdated and I have to replace the names (e.g. Attleboro Falls is now North Attleboro). There are over 500 names to replace, so I can't do it one by one. I have a table of the old city names [OriginCity] and new names [OriginMunicipality] in one table, but I want to apply them to my larger table of more than 500 observations (some of which includes no entries for cities).

Here's my code:

UPDATE [City Municipal Table] INNER JOIN MA2007_10 ON ([City Municipal Table].DestMunicipality = MA2007_10.DestCity) AND ([City Municipal Table].OriginMunicipality = MA2007_10.OriginCity) SET MA2007_10.OriginCity = [OriginMunicipality], MA2007_10.DestCity = [DestMunicipality];

City Municipal Table is the table with the old and new names lined up, MA2007_10 is the table I want to change the names in. I'm doing this for two columns, [OriginCity] and [DestCity], though they contain the same names.

Any thoughts? Thanks!

 

[Steve R.]

I have a table of the old city names [OriginCity] and new names [OriginMunicipality] in one table

I won't be able to suggest an SQL solution. I can, however, suggest a DAO recordset solution. Create a recordset based on the "MA2007_10" table and cycle through it. Create a second recordset based on your reference table. While cycling thought the "MA2007_10" table, search for the old city name in your reference table and replace it with the new city name. (You will have nested do while loops.)

This is a good reference: Comparison of DAO and ADO Recordset Syntax

 

[Kooshster]

I've been tinkering around with the code and came up with this:

update [MA2007_10], (select [City Municipal Table].OriginMunicipality, [City Municipal Table].DestMunicipality, from [City Municipal Table]) as [NewCity]
set [MA2007_10].[OriginCity]=[NewCity].[OriginMunicipality],[MA2007_10].[DestCity]=[NewCity].[DestMunicipality];

Access says there's a problem with the SELECT statement, reserved word, argument name is misspelled or missing, or the punctuation is incorrect, but for the life of me I can't see it.

 

[Scooterbug]

Add the table with the field you want to change and the table with the new names. Create an INNER JOIN between the two tables using the Old city name. Then update the field on the old city name with the field containing the new city name from the second table.

quick example attached


edit: Your last post....here is the problem:

(select

You have a (, but no closing parenthesis

 

[Kooshster]

The example certainly helps process the code, and Access claims that rows are being updated, but for the life of me the fields still aren't updating. Here's my updated code:

UPDATE MA2007_10 INNER JOIN [City Municipal Table] ON (MA2007_10.DestCity = [City Municipal Table].DestCity) AND (MA2007_10.OriginCity = [City Municipal Table].OriginCity) SET MA2007_10.OriginCity = [City Municipal Table].[OriginMunicipality], MA2007_10.DestCity = [City Municipal Table].[DestMunicipality];

 

[Scooterbug]

Try removing

(MA2007_10.DestCity = [City Municipal Table].DestCity) AND

What you are telling it to do is to join the table on both the Destination City AND the Orgininal City. If MA2007_10 doesn't have the DestCity (Which I am assuming it doesn't, else you wouldn't need an update query) that matches the DestCity from City Municipal Table then you wont get any results.

 

[Kooshster]

Ah. I decided just to split up the query into two separate queries, one for each column, and it worked! Thanks very much for all the help with the code.