crazy query

[Mickie]

I would like to say first of all thanks to all for input and help
given.
I believe I am working with a crazy query here: I have two tables
Vendors and EPLS
What I need to do is compare Vendors.First Name to EPLS.Name to see if
there is any type of match (wildcare I believe it's called) I can not
use equals because the names in the EPLS table may contain more of
less of a name than the Vendors table.
I tried the In function as well as the Like function in Access and
have not had any luck (unless I'm over looking something)
I have been working on this for the better part of two days now and
really need some help.
Thanks,
Mickie

 

[John W. Vinson]

I would like to say first of all thanks to all for input and help
given.
I believe I am working with a crazy query here: I have two tables
Vendors and EPLS
What I need to do is compare Vendors.First Name to EPLS.Name to see if
there is any type of match (wildcare I believe it's called) I can not
use equals because the names in the EPLS table may contain more of
less of a name than the Vendors table.
I tried the In function as well as the Like function in Access and
have not had any luck (unless I'm over looking something)
I have been working on this for the better part of two days now and
really need some help.
Thanks,
Mickie

Could you post an example of the kind of names you'ld like to match?

I'd expect

SELECT <whatever you want to see>
FROM Vendors INNER JOIN EPLS
ON EPLS.[Name] LIKE "*" & [Vendors].[First Name] & "*"

will find EPLS records for "John Smith" or "Marc Johnson" or anything else
containng the string "John" if First Name is equal to JOHN. Naturally there
will be lots of false drops that you'll have to deal with manually.

You say "less of a name" - that's tougher; could you give an example?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Mickie]

I would like to say first of all thanks to all for input and help
given.
I believe I am working with a crazy query here:  I have two tables
Vendors and EPLS
What I need to do is compare Vendors.First Name to EPLS.Name to see if
there is any type of match (wildcare I believe it's called)  I can not
use equals because the names in the EPLS table may contain more of
less of a name than the Vendors table.
I tried the In function as well as the Like function in Access and
have not had any luck (unless I'm over looking something)
I have been working on this for the better part of two days now and
really need some help.
Thanks,
Mickie

Could you post an example of the kind of names you'ld like to match?

I'd expect

SELECT <whatever you want to see>
FROM Vendors INNER JOIN EPLS
ON EPLS.[Name] LIKE "*" & [Vendors].[First Name] & "*"

will find EPLS records for "John Smith" or "Marc Johnson" or anything else
containng the string "John" if First Name is equal to JOHN. Naturally there
will be lots of false drops that you'll have to deal with manually.

You say "less of a name" - that's tougher; could you give an example?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

John,

Thank you for your reply; What I mean is in the Vendors table the
First Name field could have a name in it such as Audio Professionals
while the EPLS table's Name field could be Audio Professionals USA.
I need to be able to match any combination in the EPLS table against
that of the Vendors table.
Meaning select Vendor.First Name and compare it to EPLS.Name column
and if there are any similarities return the results.
I hope I'm not confusing. I just can't seem to wrap my head around
this query to figure it out.
Thanks again for your input,
Mickie

 

[John W. Vinson]

Thank you for your reply; What I mean is in the Vendors table the
First Name field could have a name in it such as Audio Professionals
while the EPLS table's Name field could be Audio Professionals USA.
I need to be able to match any combination in the EPLS table against
that of the Vendors table.
Meaning select Vendor.First Name and compare it to EPLS.Name column
and if there are any similarities return the results.

Well, taken literally, "XYZ Pro" and "ABC INC" are similar; they both contain
a blank. Would sharing a single letter be "similar"? Three letters? A "word" -
but how do you define a word? Remember, computers are very, very literal
minded: they do "identical" really well, but "Similar" is much tougher.

My suggested query would work in your example, but it would not work if you
reversed the two fields. You can get PART of the solution by doing it both
ways:

[EPLS].[Name] LIKE "*" & [Vendor].[First Name] & "*" OR
[Vendor].[First Name] LIKE "*" & [EPLS].[Name] & "*"

but that will still miss matches that are obvious to a human but not to a
computer: e.g. "Audio Professionals USA" and "Audio Professionals United
States". Neither is a proper subset of the other.

There are "fuzzy search" and "text similarity" algorithms that can help, but
there will still be a need for a USB interface - "Using Someone's Brain".
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Mickie]

Thank you for your reply;  What I mean is in the Vendors table the
First Name field could have a name in it such as Audio Professionals
while the EPLS table's Name field could be Audio Professionals USA.
I need to be able to match any combination in the EPLS table against
that of the Vendors table.
Meaning select Vendor.First Name and compare it to EPLS.Name column
and if there are any similarities return the results.

Well, taken literally, "XYZ Pro" and "ABC INC" are similar; they both contain
a blank. Would sharing a single letter be "similar"? Three letters? A "word" -
but how do you define a word? Remember, computers are very, very literal
minded: they do "identical" really well, but "Similar" is much tougher.

My suggested query would work in your example, but it would not work if you
reversed the two fields. You can get PART of the solution by doing it both
ways:

[EPLS].[Name] LIKE "*" & [Vendor].[First Name] & "*" OR
[Vendor].[First Name] LIKE "*" & [EPLS].[Name] & "*"

but that will still miss matches that are obvious to a human but not to a
computer: e.g. "Audio Professionals USA" and "Audio Professionals United
States". Neither is a proper subset of the other.

There are "fuzzy search" and "text similarity" algorithms that can help, but
there will still be a need for a USB interface - "Using Someone's Brain".
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

John,

Thanks again for the input; we are on the same page and I thought this
would be the case.
One more question, what would the query look like if I were to match
at leasr three characters in a row?
If this is even possible, thanks again,
MIckie

 

[John W. Vinson]

Thanks again for the input; we are on the same page and I thought this
would be the case.
One more question, what would the query look like if I were to match
at leasr three characters in a row?
If this is even possible, thanks again,
MIckie

I suspect it's only possible with the help of some VBA. You would need to use
VBA code to loop through each field:

Dim strMatch As String
Dim strSQL As String
Dim iPos As Integer
For iPos = 1 to Len([Name]) - 3
strMatch = Mid([Name], iPos, 3)
strSQL = "SELECT <whatever> FROM Vendors WHERE [FirstName] LIKE " _
& "'*" & strMatch & "'*"
<open a recordset based on strSQL and return the results approrpriately>
Next iPos

This will be horribly inefficient, since it must do many, many full table
scans, one for each three-letter segment.

There are better approaches - Bing or Google for "full text search" and "fuzzy
string match".
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com