Multiple Replace in the same field to replace different Names

[Never Hide]

Hello everyone,
I have a Table and in that table I have a field,named "Title_1", which contains names of different cities.I have duplicated that field and named it "Title_2". What I want to do is to raplace many of the names of the cities because from the source I got the data the names of the cities were written in an "older version" of the greek language and that creates confusion to the user when he wants to find a city.
Now I know that I can create an Update query and use the Replace function in the citeria to replace the name. The thing is that this way I'll have to create a different quey for every city name I want to replace. I know that I'll have make a new Replace for every city name but I'd like to know if it can be done in just 1 query so at least I'll save some time that way.
I know all the city names that I want to replace so I know what will be used for every replace.

Of course I'm open to any different ideas about how to do this task,if someone has any

 

[Galaxiom]

You query will be built on a join between the two tables on the fields that are the same. Then the new field is updated to the corresponding new name field for of the conversion table for each record.

 

[Never Hide]

Wait,which two tables? I have one table?

 

[Galaxiom]

I know all the city names that I want to replace so I know what will be used for every replace.

What you "know" isn't that important. How you will tell the database is the crux.

I assumed you had a table with fields for the old name and new name as records and that you wish to replace these names which are repeated multiple times on the main table. If this is the case then the you will need to make a table with the matched names.

On rereading it sounds like you simply have a list of cities you want to rename and there is only one records per name. If so there is no point whatsover making a query. Simply type the new names directly into the table.

 

[Never Hide]

Oh, sorry my fault there for the incomplete info. There isn't any record with the "new names".So, since there isn't a list that matches "old names" with "new names",I have to manually check the list of the "old names" and write the equivalent "new name".

Maybe an example of what I want to do will help make things a bit more clear

tbl_City
-------------------------------------
Title_1                 |Title_2
-------------------------------------
Iraklion                |Iraklion
Mire                    |Mire
Kalon Xorion            |Kalon Xorion

and what I'd like to do is make the tbl_City like this

tbl_City
-------------------------------------
Title_1                 |Title_2
-------------------------------------
Iraklion                |Iraklio
Mire                    |Mires
Kalon Xorion            |Kalo Xorio

there are many different names that I want to Change and all are diffeent from each other. I'm not tryig to replace multiple entries, I'm trying to replace all the "old names" that I know with "new names". The reason is that these "old names" are not used nowadays and will make searching for cities a pain in the *** for the user cause of spelling and some other issues( language related, not important for the issue at hand )

So I was wondering if it is possible to use 1 query that would implement something like the following

Replace([Title_2];"Iraklion";"Iraklio")
Replace([Title_2];"Mire";"Mires")
                  .
                  .
                  .
                  .
                  .

SQL query / Query Builder / VBA? I don't know what should I use-if that is possible at all

 

[Galaxiom]

You could use the Switch function but I still don't see the point.

Any query is going to be much more work than simply typing the name straight into the table. Why would you want to type all the old names again?

Just fill out a table with the old and new names. That is going to be less work than typing out any command in any kind of query.

 

[Never Hide]

The reaso I posted the question was to see if there was a simpler(that's quite relevant in this case ) way to do it cause I belive it'll be faster to write a query / some code or whatever than open the table-> Ctrl+H-> input "old name"-> input "new name" . This table has 13272 entries.
Anyway thank very much for your time
Any other ideas are welcome as well

 

[Galaxiom]

Unless you generate a list with the new and old names as records then there is no practical way to write a query to do this.

What is the format of the list of new and old names you are using? Access can read text files into a table. Thenyou could use a query.

 

[Never Hide]

What is the format of the list of new and old names you are using? Access can read text files into a table. Thenyou could use a query.

What do you mean? If it is "Text" or "Memo" etc?
It's "Text"

 

[Galaxiom]

Presumably you have some kind of a reference list of the old and new names for the cities.

How is that list recorded?

 

[Never Hide]

There isn't any record with the "new names".So, since there isn't a list that matches "old names" with "new names",I have to manually check the list of the "old names" and write the equivalent "new name".

 

[vbaInet]

tbl_City
-------------------------------------
Title_1                 |Title_2
-------------------------------------
Iraklion                |Iraklio[COLOR=Blue]n[/COLOR]
Mire                    |Mire
Kalon Xorion            |Kalon Xorio[COLOR=Blue]n[/COLOR]

and what I'd like to do is make the tbl_City like this

tbl_City
-------------------------------------
Title_1                 |Title_2
-------------------------------------
Iraklion                |Irakli[COLOR=Blue]o[/COLOR]
Mire                    |Mire[COLOR=Red]s[/COLOR]
Kalon Xorion            |Kalo Xori[COLOR=Blue]o[/COLOR]

You say you have over 13k records, how do you expect to match the different scenarios if you don't use table with old/new to represent the corresponding values? The example you've given above is not consistent. In the first and third records (i.e. Title_2) you removed the last character "n" but in the second record you added a character "s".

No consistency in your find/replace routine.

 

[Never Hide]

Yes, there isn't a pattern in the replacing of the names. It's names so there can't be any real pattern. The changes have to do with changes in the "newer version" of the Greek language. I'm not trying to find a way to make the changes auto-mated.I know it can't be done. But that's not what I'm asking here. What I'd like to known is if it's possible to create a query in which I'll use all the Replace function that I'll have to use, so I won't have to create a new query for every separate Replace function (using the query browser, or as an SQL statement or with something written in VBA)
and if it is possible ,
maybe give me some pointers?

 

[vbaInet]

Yes and that was answered by GalaxiomAtHome a couple of posts away -->

Just fill out a table with the old and new names. That is going to be less work than typing out any command in any kind of query.

Create a table with two fields, old and new names > link it up to your table via the old names field > convert your query to an UPDATE query > set the Update To field to the new names field and run it.