Help with Query Please
in [Help]
|
Prev: Problems with JOINs
Next: Support contracts for my apps
From: Kissi Asiedu on 10 Jul 2010 22:10 I have a table with an address field. The data in this field for example is 1234 Main St.; 1234 Main Street. I want the data to be consistent, example, all "St. Should be "Street" and "Rd" should be "Road". Can anyone please help me with a query that will change all adresses ending with "Rd" to "Road" and so on? Thanks.
From: Tom van Stiphout on 11 Jul 2010 01:40 On Sat, 10 Jul 2010 22:10:47 -0400, "Kissi Asiedu" <kissi5559(a)aol.com> wrote: You could use the Replace function: select Replace(Address, "Rd.", "Road") from myTable To make many replacements you'll want to use a VBA function and call it from your query: select myReplace(Address) from myTable Then in a standard module write your function: public function myReplace(s as string) as string s = Replace(s, "Rd.", "Road") s = Replace(s, "St.", "Street") 'etc. myReplace = s end function -Tom. Microsoft Access MVP >I have a table with an address field. The data in this field for example is >1234 Main St.; 1234 Main Street. I want the data to be consistent, example, >all "St. Should be "Street" and "Rd" should be "Road". Can anyone please >help me with a query that will change all adresses ending with "Rd" to >"Road" and so on? > >Thanks.
From: Bob Barrows on 11 Jul 2010 07:25 .... or he could nest the Replace calls and do it without a VBA function: update mytable set address=replace(replace,"Rd.","Road"),"St.","Street") Of course, it's not too bad with only two calls to Replace ... adding more would certainly make this get very messy in a hurry. To the OP: Is this planned to be a one-time update? If so, I would probably lean toward making it only do a single replacement per run, and parameterize it so I don't need to keep editing it for each replacement: update mytable set address = replace(address,[Enter string to be replaced],[Enter replacement]) Then just run it every time you find something new to replace. Tom van Stiphout wrote: > On Sat, 10 Jul 2010 22:10:47 -0400, "Kissi Asiedu" <kissi5559(a)aol.com> > wrote: > > You could use the Replace function: > select Replace(Address, "Rd.", "Road") from myTable > > To make many replacements you'll want to use a VBA function and call > it from your query: > select myReplace(Address) from myTable > > Then in a standard module write your function: > public function myReplace(s as string) as string > s = Replace(s, "Rd.", "Road") > s = Replace(s, "St.", "Street") > 'etc. > myReplace = s > end function > > -Tom. > Microsoft Access MVP > > >> I have a table with an address field. The data in this field for >> example is 1234 Main St.; 1234 Main Street. I want the data to be >> consistent, example, all "St. Should be "Street" and "Rd" should be >> "Road". Can anyone please help me with a query that will change all >> adresses ending with "Rd" to "Road" and so on? >> >> Thanks.
From: Jeff on 12 Jul 2010 04:46 Here's a different (SQL-ish) approach that may be overkill, but might be useful depending on the size of these tables and duration of growth. One advantage is you can build a simple interface to allow your client or user to add their own "renames" (Rd > Road, St > Street, Ave > Avenue, etc) without writing new Replace() functions. A client received laboratory data from many different labs, and they all had slightly different names for the same chemicals (yes, some used CAS, some didn't, I know) -- and it became a chore for me to add new entries to the find/replace code when a new Analyte name cropped up. So I added a table (tblRenameAnalyte) with two fields: Original (PK), and Rename. (Original was Primary Key because it needs to be unique.) Then join the main data table (tblChemData, for example)'s Analyte field to tblRenameAnalyte.Original, and, when an entry in tblRenameAnalyte matches Analyte, it is replaced by the Rename value. A little clunky, but I liked letting the user "teach" their own DB what to rename. Please email me if this seems potentially useful but too poorly described -- it's late! example tables: tblChemData Sample... Analyte ... ... tblRenameAnalyte Original Rename UPDATE tblChemData INNER JOIN tblRenameAnalyte ON tblChemData.Analyte = tblRenameAnalyte.Original SET tblChemData.Analyte = tblRenameAnalyte.Rename WHERE tblChemData.Analyte=tblRenameAnalyte.Original; On Jul 11, 4:25 am, "Bob Barrows" <reb01...(a)yahoo.com> wrote: > ... or he could nest the Replace calls and do it without a VBA function: > > update mytable > set address=replace(replace,"Rd.","Road"),"St.","Street") > > Of course, it's not too bad with only two calls to Replace ... adding more > would certainly make this get very messy in a hurry. > > To the OP: > Is this planned to be a one-time update? If so, I would probably lean toward > making it only do a single replacement per run, and parameterize it so I > don't need to keep editing it for each replacement: > > update mytable > set address = replace(address,[Enter string to be replaced],[Enter > replacement]) > > Then just run it every time you find something new to replace. > > > > Tom van Stiphout wrote: > > On Sat, 10 Jul 2010 22:10:47 -0400, "Kissi Asiedu" <kissi5...(a)aol.com> > > wrote: > > > You could use the Replace function: > > select Replace(Address, "Rd.", "Road") from myTable > > > To make many replacements you'll want to use a VBA function and call > > it from your query: > > select myReplace(Address) from myTable > > > Then in a standard module write your function: > > public function myReplace(s as string) as string > > s = Replace(s, "Rd.", "Road") > > s = Replace(s, "St.", "Street") > > 'etc. > > myReplace = s > > end function > > > -Tom. > > Microsoft Access MVP > > >> I have a table with an address field. The data in this field for > >> example is 1234 Main St.; 1234 Main Street. I want the data to be > >> consistent, example, all "St. Should be "Street" and "Rd" should be > >> "Road". Can anyone please help me with a query that will change all > >> adresses ending with "Rd" to "Road" and so on? > > >> Thanks.
From: Douglas J. Steele on 12 Jul 2010 07:53
What you're describing works well if you're trying to change the entire value of the field. Unfortunately, since Kissi needs to change "1234 Main St." to "1234 Main Street", you'd need to have something like: UPDATE tblChemData INNER JOIN tblRenameAnalyte ON tblChemData.Analyte Like "*" & tblRenameAnalyte.Original & "*" SET tblChemData.Analyte = Replace(tblChemData.Analyte, tblRenameAnalyte.Original, tblRenameAnalyte.Rename) (there's no reason for the WHERE clause in your SQL statement is wrong, btw, since it's already in the ON clause) However, this approach is unable to handle situations like wanting to change "1234 Main St, Apt 1A" to "1234 Main Street, Apartment 1A", should that be an aim. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "Jeff" <jnorville(a)gmail.com> wrote in message news:6c8c4070-daa7-44a6-b4ca-dd5ebeaba65a(a)a4g2000prm.googlegroups.com... Here's a different (SQL-ish) approach that may be overkill, but might be useful depending on the size of these tables and duration of growth. One advantage is you can build a simple interface to allow your client or user to add their own "renames" (Rd > Road, St > Street, Ave > Avenue, etc) without writing new Replace() functions. A client received laboratory data from many different labs, and they all had slightly different names for the same chemicals (yes, some used CAS, some didn't, I know) -- and it became a chore for me to add new entries to the find/replace code when a new Analyte name cropped up. So I added a table (tblRenameAnalyte) with two fields: Original (PK), and Rename. (Original was Primary Key because it needs to be unique.) Then join the main data table (tblChemData, for example)'s Analyte field to tblRenameAnalyte.Original, and, when an entry in tblRenameAnalyte matches Analyte, it is replaced by the Rename value. A little clunky, but I liked letting the user "teach" their own DB what to rename. Please email me if this seems potentially useful but too poorly described -- it's late! example tables: tblChemData Sample... Analyte .... .... tblRenameAnalyte Original Rename UPDATE tblChemData INNER JOIN tblRenameAnalyte ON tblChemData.Analyte = tblRenameAnalyte.Original SET tblChemData.Analyte = tblRenameAnalyte.Rename WHERE tblChemData.Analyte=tblRenameAnalyte.Original; On Jul 11, 4:25 am, "Bob Barrows" <reb01...(a)yahoo.com> wrote: > ... or he could nest the Replace calls and do it without a VBA function: > > update mytable > set address=replace(replace,"Rd.","Road"),"St.","Street") > > Of course, it's not too bad with only two calls to Replace ... adding more > would certainly make this get very messy in a hurry. > > To the OP: > Is this planned to be a one-time update? If so, I would probably lean > toward > making it only do a single replacement per run, and parameterize it so I > don't need to keep editing it for each replacement: > > update mytable > set address = replace(address,[Enter string to be replaced],[Enter > replacement]) > > Then just run it every time you find something new to replace. > > > > Tom van Stiphout wrote: > > On Sat, 10 Jul 2010 22:10:47 -0400, "Kissi Asiedu" <kissi5...(a)aol.com> > > wrote: > > > You could use the Replace function: > > select Replace(Address, "Rd.", "Road") from myTable > > > To make many replacements you'll want to use a VBA function and call > > it from your query: > > select myReplace(Address) from myTable > > > Then in a standard module write your function: > > public function myReplace(s as string) as string > > s = Replace(s, "Rd.", "Road") > > s = Replace(s, "St.", "Street") > > 'etc. > > myReplace = s > > end function > > > -Tom. > > Microsoft Access MVP > > >> I have a table with an address field. The data in this field for > >> example is 1234 Main St.; 1234 Main Street. I want the data to be > >> consistent, example, all "St. Should be "Street" and "Rd" should be > >> "Road". Can anyone please help me with a query that will change all > >> adresses ending with "Rd" to "Road" and so on? > > >> Thanks. |