Criteria using Like

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

How is a criteria written to select records in Table A that are "Like" a
field in another unlinked table B?

Thank you

Dave
 
Dave,

Try something like:

SELECT tblA.*, tblB.SearchFor
FROM tblA, tblB
WHERE tblA.SearchFieldA Like "*" & [tblB].[SearchFor] & "*"

This will identify all the records in tblA that match any of the key words
in the [SearchFor] field in table B. However, it will repeat rows if more
than one word in tblB matches the words in [SearchFieldA] of tblA. So, you
might want to do a distinct match, or Group by the ID field in a, and Count
the ID field in B, something like (not updateable):

SELECT Temp.CountOfID AS [Words found], tblSearchA.*
FROM (SELECT tblSearchA.ID, Count(tblSearchB.ID) AS CountOfID
FROM tblSearchA, tblSearchB
WHERE tblSearchA.SearchFieldA Like "*" & [tblSearchB].[SearchFor] & "*"
GROUP BY tblSearchA.ID) AS Temp INNER JOIN tblSearchA ON Temp.ID =
tblSearchA.ID;

This will
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
SELECT DISTINCT A.*
FROM A, B
WHERE A.Field Like"*" & B.Field & "*" ;

Depending on the number of records, this could take a lot of time. I'm
running it on two tables with over 100K records and it's been running for 30
minutes now.

The DISTINCT in needed to eliminate the duplicate records from the Cartisian
product.
 
Back
Top