Select stmt using Like

  • Thread starter Thread starter dpm1028
  • Start date Start date
D

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.
 
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]*";
 
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.
Click to expand...

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.
 
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 said:
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 said:
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.
Click to expand...


.
Click to expand...
 
Yes, thanks for the catch, Karl.

--

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


KARL DEWEY said:
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 said:
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 said:
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.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top