Use of Distinct when other fields in query 1

[ZOR]

I am trying to get a distinct Shot field. However the other field knocks it on the head if in the query. The other field has to be there so how does one rectify the problem? Thanks.

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

 

[dhookom]

Can you use:

Me.L12.RowSource = "SELECT TXCLIPS.Shot, TXClips.NName" _
& " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)" _
& "WHERE TXCLIPS.NName & ' ' Like '*" & Replace(Me!LNAME11.Caption, "'", "''") & "*' " _
& "AND ((Not (TXCLIPS.Shot) Is Null)) " _
& "GROUP BY TXCLIPS.Shot, TXClips.NName ORDER BY 2"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ZOR]

Thanks Duane. I just tried it, but it still shows a duplication of the shot in the list. Regards

 

[PHV]

still shows a duplication of the shot in the list
But with a different NName I hope for you ?

 

[ZOR]

Hello PHV. I see what you mean. The occurence I am suffering with is the NName field contains varients of the names contained, as records are selected by the like statement. The NName field in the database has not been used as it should have been, it should contain 1 name, but the table field has been filled with a chain of names. Turning out to be a real nightmare in search routines. Your comment has sparked me into realising what the problem is. So Duane's code would have worked if the correct data was there.
All I was trying to achieve was a distinct list of Shots in the shot field, the list is only to display a list of shots to select. Regards

 

[PHV]

So, if which NName is returned doesn't matter:

Me.L12.RowSource = "SELECT C.Shot, First(C.NName) AS SomeNName" _
& " FROM TXMASTERS M INNER JOIN TXCLIPS C ON M.ID1=C.ID1" _
& " WHERE C.NName & ' ' Like '*" & Replace(Me!LNAME11.Caption, "'", "''") & "*'" _
& " AND Not (C.Shot Is Null)" _
& " GROUP BY C.Shot ORDER BY 2"

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

 

[ZOR]

Many thanks PHV, fixed, another star.