Distinct on two fields 1

[ZOR]

I am trying to do a query where I show a DISTINCT field "shot" in a listbox, but the record that has the shot value must also have a keyword found'

The code below is producing a duplicated list of shots and
Keywords. I just want 1 Shot, providing as I say, the record has a keyword present. Thanks


Me.L12.RowSource = "SELECT DISTINCT TXCLIPS.Shot, KEYWORDS.Keyword,TXCLIPS.Comments" _
& " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)" _
& " INNER JOIN KEYWORDS ON TXCLIPS.Comments Like '*' & KEYWORDS.Keyword & ' *' " _
& "WHERE Trim(TXCLIPS.NName) Like '*" & Replace(Me!LNAME11.Caption, "'", "''") & "*' " _
& " AND Not (TXCLIPS.Shot Is Null)" _
& "AND ((Not (KEYWORDS.Keyword) Is Null)) " _
& "ORDER BY 1"

 

[PHV]

Which Keyword and Comments should be removed in case of duplicate Shot ?

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

 

[ZOR]

Thanks PHV. The penny dropped with your question. I removed Comments and Keyword from my select statement which I thought were needed for the joins to work. The result was what I was trying to do. Thankyou, regards