Aquire Like list 1

[ZOR]

Seeing the line "TXCLIPS.Comments Like "*" & KEYWORDS.keyword & " *"; below, is there a way to produce a distinct list of matching keywords? The last time I tried doing it another way it took too long and kept failing.


FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1 INNER JOIN KEYWORDS ON TXCLIPS.Comments Like "*" & KEYWORDS.keyword & " *";

Thanks

 

[PHV]

produce a distinct list of matching keywords
SELECT DISTINCT K.keyword
FROM (TXMASTERS AS M
INNER JOIN TXCLIPS AS C ON M.ID1 = C.ID1)
INNER JOIN KEYWORDS AS K ON C.Comments Like '*' & K.keyword & ' *'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ZOR]

Thanks PHV. Thats much faster than my last attempt. Regards

 

[ZOR]

Thia has come back to bite me.

I have a list that fills with a list of Keywords found in records. It becomes a pick list.

Me.L4.RowSource = "SELECT DISTINCT KEYWORDS.keyword" _
& " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)" _
& " INNER JOIN KEYWORDS ON TXCLIPS.Comments Like '*' & KEYWORDS.Keyword & ' *' " _

It obviously reduces the number of records in the database if I use the same Keyword Join in my main records query as not all records will have a matched keyword.

My problem is when I try to get records against the pick lists strList1 array, the main database query below asks for a parameter as Keyword does not exist in a join. I tried adding Keywords.Keyword in my select statement but it did not fix it. Any ideas!!

Me.LP.RowSource = "SELECT TXMASTERS.Barcode, TXCLIPS.NNAME AS Name, TXCLIPS.Comments, " _
& "TXCLIPS.Start AS TimecodeIn, TXCLIPS.Duration, TXMASTERS.SportorSports AS Sport, " _
& "TXCLIPS.StarRating, TXCLIPS.Shot, TXMASTERS.SeriesName AS Programme, " _
& "TXMASTERS.EpisodeTitle AS Episode, TXMASTERS.Competition" _
& " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)" _
& "WHERE (((TXMASTERS.SportorSports) Like [FORMS]![NewQuerysForm].[LNAME].[CAPTION]))" _
& "AND KEYWORDS.Keyword IN (" & Mid(strList1, 2) & ")"


Thanks

 

[ZOR]

Since thought about it logically. If a keyword is selected then that will reduce records, so the join can go in that query. If no keywords picked, then it goes through a query without the keyword join. Working too late. Thanks