 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2006Compare two fields, find near match
Hi all, I am trying to compare the information in one field to the information in another (basically an automated find/replace, one table to another): Field1 Field2 (Replace With) belmont park n | Belmonte Park N diamond ave. | Diamond Ave e. third st. | E Third St I have a search query that does something similar with one table, one field: Like "*" & [Table2].[Field2] & "*" But it does not do what I want it to do here. This looks at the input and finds the nearest match based on the whole field. I would like to look within the both fields and do something similar: Field1 Field2 <belmont> park n | <Belmont>e Park N <diamond> ave. | <Diamond> Ave e. <third> st. | E <Third> St This would narrow down my choices: Belmonte Park N or Belmonte Park E Diamond Ave E Third St or W Third St Hope I have explained my problem well enough. Thanks in advance for your help. NickX Nick This will really depend on: 1) how you define "near match" 2) the level of your coding skills In most instances I've run across where folks are trying for a "close match", the best way to achieve this is USB (using someone's brain). For example, would YOU (not a program function) consider the following addresses to be a close match? 12345 Elm St 12345 Elm Street 12345 Elm Street SE 12345 Elm Ave 12354 Elm St 12345 Elm St, Apt 103  SignatureRegards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ > Hi all, > I am trying to compare the information in one field to the information in [quoted text clipped - 24 lines] > Thanks in advance for your help. > NickX Thank you for your response, David Mueller suggested instr() This is not actually address matching, it is matching of the street names only. From your example, I would only want to find "Elm" whether it is N Elm St, Elm Dr, or Elm Tree Lane. The rest would be done by USB. Right now I am working with about 100 records. Eventually I will have to apply this to over 40,000 records. My coding skills are mediocre to advanced in most areas and the rest I can fake. Thanks, NickX > Nick > [quoted text clipped - 44 lines] > > Thanks in advance for your help. > > NickX Problems with address matching aside, ... If I understand what you're explaining, I think you want to look at using the instr() function. It tells you at which position within the field your search string can be found. Once you find (select) the field containing your search string, and the position and length of what you want to replace, then you can manipulate a temp string and replace the field's value. You can use the instr() in either the select list or where clause, or both - wherever it makes sense for what you're doing. HTH, David > Hi all, > I am trying to compare the information in one field to the information in [quoted text clipped - 24 lines] > Thanks in advance for your help. > NickX Great, this is the direction I was thinking. So, if the first character equals N<space>, S<space>, E<space>, or W<space>; then start after the first <space> and go to the second <space>. If not then start at the first chararcter and and go to the first <space>. I've never quite grasped the instr() thing, I will give it a try. Any suggestions would be greatly appreciated. > Problems with address matching aside, ... > [quoted text clipped - 40 lines] > > Thanks in advance for your help. > > NickX I'm not sure about your description below, or where you're headed. "N<space>" is two characters: an "N", and a "<space>" But to confirm, the instr() will find an occurence of one string within another string, and starting at any position within the search string. You may also want to check out the Split function; however, I'm not sure if you can use the Split function in a SQL statement since it returns an array. > Great, this is the direction I was thinking. > So, if the first character equals N<space>, S<space>, E<space>, or W<space>; [quoted text clipped - 48 lines] > > > Thanks in advance for your help. > > > NickX Yes: > "N<space>" is two characters: an "N", and a "<space>" > > So, if the first character equals N<space>, S<space>, E<space>, or W<space>; > > then start after the first <space> and go to the second <space>. If not then > > start at the first chararcter and and go to the first <space>. This would be a combination of instr() and IIf() (N,S,E,W being directions North, South... with a space character after). So, if the first two characters are a direction and a space start at the third character and go to the next space character. Otherwise, start at the first character and go to the first space character. IIf(Table1.Field1=N * or Table1.Field1=S * or Table1.Field1=E * or Table1.Field1=W *,instr(starting at third character),instr(starting at first character)) It seems to me that something like this should work, if I knew how to word it correctly. Hopefully, I have described dilemma better this time. Thanks, NickX This takes care of the first part: Trim(Mid([GEONAME],InStr(1,[GEONAME]," ")+1,InStr(InStr(1,[GEONAME]," ")+1,[GEONAME]," ")-InStr(1,[GEONAME]," "))) Criteria: [GEONAME] Like "N *" Or [GEONAME] Like "E *" Or [GEONAME] Like "S *" Or [GEONAME] Like "W *" This takes care of the second part: Left([GEONAME],InStr(1,[GEONAME]," ")-1) Criteria: [GEONAME] Not Like "N *" And [GEONAME] Not Like "E *" And [GEONAME] Not Like "S *" And [GEONAME] Not Like "W *" But do I put it all together? >> But do I put it all together? Sure, why not. From here, it's like superhero thing: you have the knowledge, now use it for good or evil. As Jeff Boyce pointed out in his post, address manipulation is evil :) You know, maybe you're better off checking out the Replace function. Have you seen that one? > This takes care of the first part: > Trim(Mid([GEONAME],InStr(1,[GEONAME]," ")+1,InStr(InStr(1,[GEONAME]," [quoted text clipped - 10 lines] > > But do I put it all together? I finally figured out how to put it all together into one ugly little SQL statement, but I can't really figure out how to use it?!? For now I'm not sure, I think I just spent a couple of days writing a useless SQL statement. Maybe someday in the though, I can figure out how to use my powers for good. SQL for Finding Root Street Name (most of it, anyway): SELECT Table1.STREETNAME, IIf([STREETNAME] Like "N *" Or [STREETNAME] Like "E *" Or [STREETNAME] Like "S *" Or [STREETNAME] Like "W *",Trim(Mid([STREETNAME],InStr(1,[STREETNAME]," ")+1,IIf(InStr(InStr(1,[STREETNAME]," ")+1,[STREETNAME]," ")=0,0,InStr(InStr(1,[STREETNAME]," ")+1,[STREETNAME]," ")-InStr(1,[STREETNAME]," ")))),Left([STREETNAME],InStr(1,[STREETNAME]," ")-1)) AS ROOT_NAME, Table1.USED_CITY FROM Table1 WHERE (((Table1.USED_CITY)="yes")); Hopefully, this may help someone else. It helped me figure out how to put together outrageous SQL strings. Thank you for your help, NickX If you want to update something, just write an UPDATE statement and use your SELECT inside the UPDATE statement. Quite frankly, though, I think you will have the potential to "destroy" as many addresses as you'll fix. You may find more value in other ways of address verification. For example, assuming you are in the United States and if you haven't already, add a field for and capture the +4 of the address' postal (zip) code. Later, David > I finally figured out how to put it all together into one ugly little SQL > statement, but I can't really figure out how to use it?!? For now I'm not [quoted text clipped - 17 lines] > Thank you for your help, > NickX Now David, I wouldn't go THAT far. I do find address manipulation to be very, very irritating, though <g>. Jeff > >> But do I put it all together? > [quoted text clipped - 19 lines] > > > > But do I put it all together? try the Firefly SQL Comparison Tool. it's a free tool that i wrote: http://www.getfirefly.net/ (scroll to the bottom) let me know if you have any problems. thanks, James |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.