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

On Fri, 23 Sep 2011 12:04:05 -0700 (PDT), Mickie <(E-Mail Removed)>
wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Sep 23, 3:26*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Fri, 23 Sep 2011 12:04:05 -0700 (PDT), Mickie <mickiemell...@gmail.com>
> wrote:
>
> >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/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../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

On Sat, 24 Sep 2011 20:12:44 -0700 (PDT), Mickie <(E-Mail Removed)>
wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Sep 25, 1:26*am, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Sat, 24 Sep 2011 20:12:44 -0700 (PDT), Mickie <mickiemell...@gmail.com>
> wrote:
>
> >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/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../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

On Mon, 26 Sep 2011 06:58:27 -0700 (PDT), Mickie <(E-Mail Removed)>
wrote:


>
>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com