Subquery SQL syntax help

[Gina K]

The following is the first subquery I’ve ever used. It’s coming very close
to what I want, except if there is a tie in the TimeResult field. What I'd
like is to assign a unique number (Rank) to each record in the group based on
TimeResult (ascending). If there is a tie in TimeResult, break that tie
alphabetically, i.e. if Smith and Jones are tied at 1120, then assign Jones
the lower rank.

Can anyone help me?
Thanks.

SELECT tblEventResults.fkAthlete, tblEventResults.fkMeetEventID,
tblEventResults.TimeResult, (SELECT Count(*) FROM tblEventResults AS T
WHERE T.TimeResult < tblEventResults.TimeResult AND T.fkMeetEventID =
tblEventResults.fkMeetEventID)+1 AS AutoPlace, tblMeetEvents.fkMeetID,
tblAthletes.School, tblEventResults.ResultType
FROM tblMeet INNER JOIN ((tblEventLookup INNER JOIN tblMeetEvents ON
tblEventLookup.EventName = tblMeetEvents.Event) INNER JOIN (tblAthletes INNER
JOIN tblEventResults ON tblAthletes.Athlete = tblEventResults.fkAthlete) ON
tblMeetEvents.MeetEventID = tblEventResults.fkMeetEventID) ON tblMeet.MeetID
= tblMeetEvents.fkMeetID
WHERE (((tblEventResults.ResultType)="Pre-Meet"))
ORDER BY tblEventResults.TimeResult, tblEventResults.fkAthlete;

 

[Ken Snell \(MVP\)]

Add an ORDER BY clause to the subquery that will break the tie (usually it's
best to use the primary key because that will guarantee no tie):

(SELECT Count(*) FROM tblEventResults AS T
WHERE T.TimeResult < tblEventResults.TimeResult AND T.fkMeetEventID =
tblEventResults.fkMeetEventID
ORDER BY T.fkAthlete)

 

[Ken Snell \(MVP\)]

Sorry --

Posted wrong info in post just sent.

I believe you can do this by using an additional subquery to adjust the
Count result from the first subquery:

(SELECT Count(*) +
(SELECT Count(*) AS AdjustCount
FROM tblEventResults AS TT
WHERE TT.TimeResult < tblEventResults.TimeResult AND
TT.fkMeetEventID = tblEventResults.fkMeetEventID AND
TT.fkAthlete < tblEventResults.fkAthlete)
FROM tblEventResults AS T
WHERE T.TimeResult < tblEventResults.TimeResult AND T.fkMeetEventID =
tblEventResults.fkMeetEventID)


This may slow down your query a bit, though.

 

[Gina K]

Thanks for the response, Ken. However, when I tried your suggestion, I ended
up with this:

Athlete TimeResultAutoPlace Expr1
Washin 1140 1 0
Singer 1150 2 1
Harris 1160 3 2
Malloy 1160 3 2
Zierelli 1170 5 8
Leichty 1180 6 6
Marks 1190 7 9
Davis 1190 7 6
Smith 1200 9 14
Walker 1210 10 16
Hillgartner 1230 11 12
Trump 1240 12 19
Holzer 1250 13 15
Goetz 1260 14 14
Shuba 1260 14 20
Holmes 1270 16 19
Leoni 1280 17 23
Luckasevic 1290 18 25
Wilson 1300 19 35
Lisovich 1330 20 27
Cooper 1350 21 20
Schultz 1410 22 34
Field 1420 23 24

Any other ideas?
Thanks again.

 

[Ken Snell \(MVP\)]

Post the SQL statement of the query that you tried.

--

Ken Snell
<MS ACCESS MVP>

Thanks for the response, Ken. However, when I tried your suggestion, I
ended
up with this:

Athlete TimeResultAutoPlace Expr1
Washin 1140 1 0
Singer 1150 2 1
Harris 1160 3 2
Malloy 1160 3 2
Zierelli 1170 5 8
Leichty 1180 6 6
Marks 1190 7 9
Davis 1190 7 6
Smith 1200 9 14
Walker 1210 10 16
Hillgartner 1230 11 12
Trump 1240 12 19
Holzer 1250 13 15
Goetz 1260 14 14
Shuba 1260 14 20
Holmes 1270 16 19
Leoni 1280 17 23
Luckasevic 1290 18 25
Wilson 1300 19 35
Lisovich 1330 20 27
Cooper 1350 21 20
Schultz 1410 22 34
Field 1420 23 24

Any other ideas?
Thanks again.

 

[Ken Snell \(MVP\)]

Found a logic error in the sub-subquery -- that query needs to use = for the
time field comparison, not <. Try this SQL statement for your whole query:

SELECT tblEventResults.fkAthlete,
tblEventResults.fkMeetEventID,
tblEventResults.TimeResult,
(SELECT Count(*) +
(SELECT Count(*) AS AdjustCount
FROM tblEventResults AS TT
WHERE TT.TimeResult = tblEventResults.TimeResult AND
TT.fkMeetEventID = tblEventResults.fkMeetEventID AND
TT.fkAthlete < tblEventResults.fkAthlete)
FROM tblEventResults AS T
WHERE T.TimeResult < tblEventResults.TimeResult AND T.fkMeetEventID =
tblEventResults.fkMeetEventID)+1 AS AutoPlace,
tblMeetEvents.fkMeetID,
tblAthletes.School,
tblEventResults.ResultType
FROM tblMeet INNER JOIN ((tblEventLookup INNER JOIN tblMeetEvents ON
tblEventLookup.EventName = tblMeetEvents.Event) INNER JOIN (tblAthletes
INNER
JOIN tblEventResults ON tblAthletes.Athlete = tblEventResults.fkAthlete) ON
tblMeetEvents.MeetEventID = tblEventResults.fkMeetEventID) ON tblMeet.MeetID
= tblMeetEvents.fkMeetID
WHERE (((tblEventResults.ResultType)="Pre-Meet"))
ORDER BY tblEventResults.TimeResult, tblEventResults.fkAthlete;
--

Ken Snell
<MS ACCESS MVP>

Thanks for the response, Ken. However, when I tried your suggestion, I
ended
up with this:

Athlete TimeResultAutoPlace Expr1
Washin 1140 1 0
Singer 1150 2 1
Harris 1160 3 2
Malloy 1160 3 2
Zierelli 1170 5 8
Leichty 1180 6 6
Marks 1190 7 9
Davis 1190 7 6
Smith 1200 9 14
Walker 1210 10 16
Hillgartner 1230 11 12
Trump 1240 12 19
Holzer 1250 13 15
Goetz 1260 14 14
Shuba 1260 14 20
Holmes 1270 16 19
Leoni 1280 17 23
Luckasevic 1290 18 25
Wilson 1300 19 35
Lisovich 1330 20 27
Cooper 1350 21 20
Schultz 1410 22 34
Field 1420 23 24

Any other ideas?
Thanks again.

 

[Gina K]

That did the trick! Thanks a million.

 

[Ken Snell \(MVP\)]

You're welcome.