Compare two fields, find near match

[Nick X]

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

 

[Jeff Boyce]

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

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[David Mueller]

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

 

[Nick X]

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, ...

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
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 X]

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

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

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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
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

 

[David Mueller]

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>;
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, ...

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
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 X]

Yes:

"N<space>" is two characters: an "N", and a "<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

 

[Nick X]

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?

 

[David Mueller]

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?

 

[Nick X]

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

 

[David Mueller]

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

 

[Jeff Boyce]

Now David, I wouldn't go THAT far.

I do find address manipulation to be very, very irritating, though <g>.

Jeff

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],"
")+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?

 

[knightconsulting]

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