Select stmt using Like

[dpm1028]

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.

 

[Ken Snell]

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]*";

 

[John W. Vinson]

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.

 

[KARL DEWEY]

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

--
Build a little, test a little.

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/

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.

.

 

[Ken Snell]

Yes, thanks for the catch, Karl.

--

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

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

--
Build a little, test a little.

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/

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.

.