Select stmt using Like

I have 2 tables in an db. Contacts is one with field LastName. The other is
Issues. The issues db has a free text field that stores lastname/lastname...
the field is called Owner. The objective is for multiple owners in an issue,
but not necessarily from the Contacts table.
I am trying to write a select statement that will allow me to compare the
Contacts.LastName with any Issues.Owner.
Select *
From Issues
Where Issues.Owner LIKE *[Contacts.lastname]* (except I cannot use this
format)
Any ideas would be appreciated.
--
dan

You can use a non-equi-join query (though it'll be slow with large amounts
of data):

Select *
From Issues INNER JOIN Contacts
ON Issues.Owner LIKE "*[Contacts.lastname]*";

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"dpm1028" wrote in message
...
I have 2 tables in an db. Contacts is one with field LastName. The other
is
Issues. The issues db has a free text field that stores
lastname/lastname...
the field is called Owner. The objective is for multiple owners in an
issue,
but not necessarily from the Contacts table.
I am trying to write a select statement that will allow me to compare the
Contacts.LastName with any Issues.Owner.
Select *
From Issues
Where Issues.Owner LIKE *[Contacts.lastname]* (except I cannot use this
format)
Any ideas would be appreciated.
--
dan

On Mon, 10 May 2010 17:54:01 -0700, dpm1028
wrote:

I have 2 tables in an db. Contacts is one with field LastName. The other is
Issues. The issues db has a free text field that stores lastname/lastname...
the field is called Owner. The objective is for multiple owners in an issue,
but not necessarily from the Contacts table.
I am trying to write a select statement that will allow me to compare the
Contacts.LastName with any Issues.Owner.

Try

Select Issues.*
From Issues INNER JOIN Contacts
ON Issues.Owner LIKE "*" & [Contacts].[lastname] & "*"


This will give undesired results if there are LastName values such as "Wu" or
"Lo" or "Lee", since those text strings could appear in other parts of the
freeform text.
--

John W. Vinson [MVP]

Would it not be like this --
Select *
From Issues INNER JOIN Contacts
ON Issues.Owner LIKE "*" & [Contacts.lastname] & "*";

--
Build a little, test a little.


"Ken Snell" wrote:

You can use a non-equi-join query (though it'll be slow with large amounts
of data):

Select *
From Issues INNER JOIN Contacts
ON Issues.Owner LIKE "*[Contacts.lastname]*";

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"dpm1028" wrote in message
...
I have 2 tables in an db. Contacts is one with field LastName. The other
is
Issues. The issues db has a free text field that stores
lastname/lastname...
the field is called Owner. The objective is for multiple owners in an
issue,
but not necessarily from the Contacts table.
I am trying to write a select statement that will allow me to compare the
Contacts.LastName with any Issues.Owner.
Select *
From Issues
Where Issues.Owner LIKE *[Contacts.lastname]* (except I cannot use this
format)
Any ideas would be appreciated.
--
dan


.

Yes, thanks for the catch, Karl.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"KARL DEWEY" wrote in message
...
Would it not be like this --
Select *
From Issues INNER JOIN Contacts
ON Issues.Owner LIKE "*" & [Contacts.lastname] & "*";

--
Build a little, test a little.


"Ken Snell" wrote:

You can use a non-equi-join query (though it'll be slow with large
amounts
of data):

Select *
From Issues INNER JOIN Contacts
ON Issues.Owner LIKE "*[Contacts.lastname]*";

--

Ken Snell
http://www.accessmvp.com/KDSnell/



"dpm1028" wrote in message
...
I have 2 tables in an db. Contacts is one with field LastName. The
other
is
Issues. The issues db has a free text field that stores
lastname/lastname...
the field is called Owner. The objective is for multiple owners in an
issue,
but not necessarily from the Contacts table.
I am trying to write a select statement that will allow me to compare
the
Contacts.LastName with any Issues.Owner.
Select *
From Issues
Where Issues.Owner LIKE *[Contacts.lastname]* (except I cannot use this
format)
Any ideas would be appreciated.
--
dan


.