Sorting a complex query in ascending order

[neelsfer]

This query is for a race taking place over 3 days.

The query adds up 100% and i can get the total times of athletes.

My only problem i have, is to sort the race times depending on whether a competitor finish the race or not.

If all the athletes are suppose to complete 3 days, and one athlete only manage to finish 2 of the 3, then that person's finish position in the race, should be last .

In the attached screen pic, it can be seen that RaceNo 3 (Ian Adams) did not complete day3time (third day of the race.)His time is therefore actually the worst of the 3 athletes, but now the shortest as he only did 2 days.

Looking at the Totaltime for the 3 athletes, RaceNo=2 (Shawn) has the best totaltime = 01:30(shortest for the 3 days), followed by RaceNo1 =(Michael) in 01.31 and last is RaceNo3 because he failed to do Day3time.

My question: how can i sort RaceNo 2 and 3 in ascending order, and if you have a "blank time" for an athlete, then that name appear last in the query?
I also include the actual query problem
pls help

[ADezii]

Let me know if this is what you are looking for, and if it is, I'll explain the Logic, if need be of course.

[neelsfer]

I have changed the tables but its still the same problem.
What i did not mention is that there can be up to 8 different times per RaceNo
If any Raceno (athlete) did not finish one or more of the "laps" , then that athlete's time should be at the bottom when the query is done.
I attached a new screen pic and adjusted database for 8 laps

My problem:
Only RaceNo 1 and 2 finished all the laps ie 8 in this case.
RaceNo 3 and 4 did not finish all 8.
I must sort on RaceNo 1 and 2 to get the overall positions in the race and RaceNo3 and 4 must appear after RaceNo 1& 2 in no specific order.
In the above example, RaceNo = 2 time 00:1:14 ( is the winner and should be on top followed by RaceNo =1 (00:1:18) etc
i wish this was a straight forward sort but these blank times create complications

[Rabbit]

Couldn't you group by a flag that determined whether or not they competed in all three races?

[neelsfer]

in this instance the athlete has to complete 8 laps, but because of injury etc they may end up doing perhaps 6 only.
I have to sort those that do finish by their total time.

[Rabbit]

I know, that's why you should group by a flag. Either one that you create in the table or one that is calculated on the fly.

[OldBirdman]

Why not add some amount of time, say 1 hour or 1 day, instead of 0 for any lap not completed. When you sort from lowest (fastest) time to highest(slowest), the times with 1 day added will be at the bottom of the list.
This will also rank those who only participated on some days. For your 3 day event, if an athelete competed 2 days, their time would be faster than someone who only competed 1 day, even if their 1 day time were slower than the average of the 2 day times.

[ADezii]

I need to see a fully functional RT_XCAllLapsQ2 Query even if there are errors and the Records are not in the correct sequence. You latest Attachment does not show this.

[neelsfer]

Got it right!! Thx to Mr "amazing" Adezzi.

I adapted his last post a bit and its working!

Sort the "blank" fields with this code (ascending), where 1 is when you have a time and 10 if its blank. The athletes that finished all the required laps are at the top and vice versa

Sort: IIf(Not IsNull([lap1]),IIf(Not IsNull([lap2]),IIf(Not IsNull([lap3]),"1","10")))

sort the "times" with this code in secondary ascending sorting

TotTime: Format$(IIf(IsNull([Lap1]-[Actualstarttime]),0,CDate([Lap1]-[ActualStartTime]))+IIf(IsNull([Lap2]-[Lap1]),0,CDate([Lap2]-[Lap1]))+IIf(IsNull([Lap3]-[Lap2]),0,CDate([Lap3]-[Lap2])),"hh:nn:ss")

The winner will be at the top

i will now adapt it further for 8 different lap times in total