Maintain Distinct 1

[ZOR]

How can I include the Athlete.ID in this query without losing my distinct list of Athlete names? Thanks

STRSQL = ""
STRSQL = "SELECT DISTINCT Athletes.Athlete, Athletes.Surname, Athletes.ID FROM Athletes "
STRSQL = STRSQL & "WHERE (((Athletes.Athlete) Like [Forms]![AthleteCompetition].[Form].[LNAME2].[Caption]))"
STRSQL = STRSQL & "ORDER BY Athletes.Athlete;"
Me.List38.RowSource = STRSQL

 

[lameid]

Why would you want to? I mean what good is the ID if you are may not get the one you need?

You could group by the name instead of selecting distinct and then use any of first, last, min or max on the Account ID field.

 

[ZOR]

Thanks. The Athlete table is a standalone table, created from running a query on two linked tables, one of which contains a single field (Athlete)which users put more than one name in seperated by commas, apostrophies etc. Badly entered data. I have added all the names as single entries into the standalone table. The Athletes ID contains the primary key number of the parent table which I want to extract for later use. So summarising, my Distinct list should present a distinct list in a listbox, but contain the Athletes ID field. Your suggestion of grouping sounds sensible, but in the query design window how do I get the grouping option to appear in the design grid? Thanks

 

[lameid]

On the toolbar in QBE it is the capital Sigma button or the funny looking E.

 

[ZOR]

Many thanks lameid, I'm moving again. Have a good weekend.