If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Subqueries and ranking
I'm ranking records in a query, but I'm having a little trouble getting the
ties to display the way I want. Here's the sql: SELECT tblEventResults.fkAthlete, tblEventResults.fkMeetEventID, tblEventResults.STimeResult AS Expr1, (SELECT Count(*) FROM tblEventResults AS T WHERE tblEventResults.STimeResult+1T.STimeResult) AS FinishPlace FROM tblEventResults WHERE (((tblEventResults.fkMeetEventID)=[Forms]![sbfSGrpTimeEntry]![fkMeetEventID])) ORDER BY tblEventResults.STimeResult ASC, tblEventResults.fkAthlete; My results are numbered 1, 3, 3, 4, 5, etc., but I want them to be 1, 2, 2, 4, 5, etc. Can someone help? Thanks. |
#2
|
|||
|
|||
Subqueries and ranking
"Gina K" wrote in message
: I'm ranking records in a query, but I'm having a little trouble getting the ties to display the way I want. Here's the sql: SELECT tblEventResults.fkAthlete, tblEventResults.fkMeetEventID, tblEventResults.STimeResult AS Expr1, (SELECT Count(*) FROM tblEventResults AS T WHERE tblEventResults.STimeResult+1T.STimeResult) AS FinishPlace FROM tblEventResults WHERE (((tblEventResults.fkMeetEventID)=[Forms]![sbfSGrpTimeEntry]![fkMeetEventID])) ORDER BY tblEventResults.STimeResult ASC, tblEventResults.fkAthlete; My results are numbered 1, 3, 3, 4, 5, etc., but I want them to be 1, 2, 2, 4, 5, etc. Can someone help? Thanks. Here's one thing to try, move the "+1" part outside the subquery WHERE tblEventResults.STimeResult T.STimeResult) +1 AS FinishPlace -- Roy-Vidar |
#3
|
|||
|
|||
Subqueries and ranking
Try the following. I've included the fkMeetEventID on the guess that you
want to rank the finishers in the event. Note that this calculates first place as 0, second as 1, etc. So we just add 1 to the result to get 1, 2, 2, 4, 5 SELECT tblEventResults.fkAthlete , tblEventResults.fkMeetEventID , tblEventResults.STimeResult AS Expr1 , (SELECT Count(*) FROM tblEventResults AS T WHERE T.STimeResult tblEventResults.STimeResult AND T.fkMeetEventID = tblEventResults.fkMeetEventID) + 1 AS FinishPlace FROM tblEventResults WHERE (((tblEventResults.fkMeetEventID)=[Forms]![sbfSGrpTimeEntry]![fkMeetEventID])) ORDER BY tblEventResults.STimeResult ASC, tblEventResults.fkAthlete; -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Gina K" wrote in message ... I'm ranking records in a query, but I'm having a little trouble getting the ties to display the way I want. Here's the sql: SELECT tblEventResults.fkAthlete, tblEventResults.fkMeetEventID, tblEventResults.STimeResult AS Expr1, (SELECT Count(*) FROM tblEventResults AS T WHERE tblEventResults.STimeResult+1T.STimeResult) AS FinishPlace FROM tblEventResults WHERE (((tblEventResults.fkMeetEventID)=[Forms]![sbfSGrpTimeEntry]![fkMeetEventID])) ORDER BY tblEventResults.STimeResult ASC, tblEventResults.fkAthlete; My results are numbered 1, 3, 3, 4, 5, etc., but I want them to be 1, 2, 2, 4, 5, etc. Can someone help? Thanks. |
#4
|
|||
|
|||
Subqueries and ranking
John,
You read my mind - how did you know what I wanted even before I did? Thanks again! "John Spencer" wrote: Try the following. I've included the fkMeetEventID on the guess that you want to rank the finishers in the event. Note that this calculates first place as 0, second as 1, etc. So we just add 1 to the result to get 1, 2, 2, 4, 5 SELECT tblEventResults.fkAthlete , tblEventResults.fkMeetEventID , tblEventResults.STimeResult AS Expr1 , (SELECT Count(*) FROM tblEventResults AS T WHERE T.STimeResult tblEventResults.STimeResult AND T.fkMeetEventID = tblEventResults.fkMeetEventID) + 1 AS FinishPlace FROM tblEventResults WHERE (((tblEventResults.fkMeetEventID)=[Forms]![sbfSGrpTimeEntry]![fkMeetEventID])) ORDER BY tblEventResults.STimeResult ASC, tblEventResults.fkAthlete; -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. |
Thread Tools | |
Display Modes | |
|
|