Question how to remove rows with one duplicate field in a "rank"

[burnfly]

Hello all. A bit of a novice user question here. I have a competition ranking contestants. A contestant can compete multiple times, each time with a unique registration number.

I am trying to sort by score to rank the contestants, however the contestants can only receive one rank - their highest score (not a sum of their scores, only one score). ? ?

Example:
Registration Name Score
2345 Sally 247
3456 George 230
4672 Sally 255

What I want to see:
4672 Sally 255
3456 George 230

Thanks!

 

[jdraw]

Please show us the query (SQL) that you have built.

I mocked up your sample in tblRunners


Here's the query
SELECT tblRunners.RegNo, tblRunners.pName, tblRunners.pScore
FROM tblRunners INNER JOIN
(SELECT tblRunners.pName, Max(tblRunners.pScore) AS MaxOfpScore
FROM tblRunners
GROUP BY tblRunners.pName) AS x ON (tblRunners.pScore =X.MaxOfpScore) AND
(tblRunners.pName = X.pName)
ORDER BY tblRunners.pScore Desc


and the result:

RegNo	pName	pScore
4672	Sally	255
3456	George	230

 

[burnfly]

Warning - this is massive.

SELECT Events.EventID, Events.EventName, Events.EventDescription, Events.Location, [Fee Schedules].FeeDescription, Categories.CategoryName, Registration.RegistrationID, AgeDivisions.AgeDivisionDesc, AgeGroup.AgeGroup, Registration.EntryNumber, Registration.RoutineName, Attendees.CompanyName, qryJudge01Totals.Judge01TotalScore, qryJudge02Totals.Judge02TotalScore, qryJudge03Totals.Judge03TotalScore, Participants.ParticipantName, [Judge01TotalScore]+[Judge02TotalScore]+[Judge03TotalScore] AS ScoresTotal
FROM Events INNER JOIN (Attendees INNER JOIN (((((AgeDivisions RIGHT JOIN (([Fee Schedules] RIGHT JOIN (Categories RIGHT JOIN (AgeGroup RIGHT JOIN Registration ON AgeGroup.AgeGroup = Registration.AgeGroup) ON Categories.CategoryID = Registration.Category) ON [Fee Schedules].FeeScheduleID = Registration.FeeScheduleID) LEFT JOIN tblDivisions ON [Fee Schedules].FeeDescription = tblDivisions.DivisionDescribe) ON AgeDivisions.AgedivID = Registration.[Age Division]) LEFT JOIN qryJudge01Totals ON Registration.RegistrationID = qryJudge01Totals.RegistrationID) LEFT JOIN qryJudge02Totals ON Registration.RegistrationID = qryJudge02Totals.RegistrationID) LEFT JOIN qryJudge03Totals ON Registration.RegistrationID = qryJudge03Totals.RegistrationID) LEFT JOIN Participants ON Registration.RegistrationID = Participants.RegistrationID) ON Attendees.AttendeeID = Registration.AttendeeID) ON Events.EventID = Registration.EventID
WHERE (((Events.EventID)=[Forms]![frmScoringSelect]![cboEventID]) AND (([Fee Schedules].FeeDescription)="Solo Advanced") AND ((AgeDivisions.AgeDivisionDesc)="Senior"))
ORDER BY [Judge01TotalScore]+[Judge02TotalScore]+[Judge03TotalScore] DESC;

 

[jdraw]

I responded/edited in between your posts.

 

[burnfly]

Thanks a gazillion! That did the trick!

 

[jdraw]

You are welcome-- happy to help.