Sorting a complex query in ascending order

[neelsfer]

I have this cycling timing program. I get the correct data from my start and finish calculations, but is struggling to sort the total time in a report.

It works like this.
- A cyclist do laps around a course - up to 8 laps depending on age grouping.
-When the first rider finishes his/her total number of laps - shown as "totallaps" in report, the other riders have to stop riding, on completing the current lap they are busy riding. This may mean that somebody is on ie lap 6 while others may be busy with 5 or 4 or even pull out of the race if they are lapped.
The overall winner does all the laps, and the next rider with the 2nd most laps done in shortest time, is 2nd overall etc.
i have a screen pic included where I added finish positions 1-10 to explain the positions and laps and times.
The database is also included.
The principle is, if somebody does not complete the "totallaps" as required (and listed in report), then their results should appear below the cyclist who did complete the correct number of totallaps. The problem is that those that did not finish the required number of laps, will have a shorter totaltime, making sorting impossible.

My thinking is to add a "1" to another "query field" that is linked to a specific laptime field, if the rider does finish it and a "0" if they did not do a lap. You then "add up" the laps done and descending sort them first, followed by total time in ascending order.

I struggle with that "not is null 1,0" coding in the query.
Tried this without success

Sortlap1: IIf(Not IsNull([lap1time]),"1","0")

please help

[gershwyn]

Sortlap1: IIf(Not IsNull([lap1time]),"1","0")

There is nothing wrong with your syntax (though I would ditch the 'not' and reverse the 1 and 0.) You said you weren't successful with it - in what way? Are you getting an error, or is it not doing what you intended?

You didn't give any detail about how the data is stored in the table, but it's simple enough to sort that way given a normalized table structure. Assuming a table with RacerID, LapNumber, and LapTime fields, you could sort them like this:

SELECT RacerID, Count(LapNumber) as LapsCompleted, Sum(LapTime) as TotalTime FROM RaceStats
WHERE LapTime Is Not Null
ORDER BY LapNumber DESC, LapTime;

I'm guessing from your use of the field name lap1time that you have all 8 laps as separate fields in your table, which makes things more complicated - but I don't have enough information to help in that department.

[neelsfer]

Hi gershwyn.
The blank columns don't give me a "zero" value and the columns that do have a laptime, should be giving me a value of "1".
The attached example above has a table from which the report is created using a query(same format/fields i used). I appended the results to it from another query, after subtracting finish and start times per lap. Its now just a case of displaying the results in a specific order.

[gershwyn]

Hi neelsfer,

I am not in a position to download your example. Please post the relevant details as text in your post. (It is also a good idea to post which version of Access you are using. I'm sure there are others who might take a look, but it's frustrating to download something only to find you can't open it anyway. I use 2007 but I know several experts on here are sticking with older versions.)

I still don't have much to go on, but it may be that your blank columns are not null, but empty strings. If that is the case, your IsNull is always going to return false, and you will always get a "1" as a result. You can test for both null and empty strings in one test:

IIF(Len(Nz([lap1time], "")) > 0, "1", "0")

[neelsfer]

thx its calculating the 0 and 1 correctly now

[neelsfer]

When i add up the cumulative values of these fields using
ie

sortlap1:IIF(Len(Nz([lap1time], "")) > 0, "1", "0")

up to sortlap8

Totalsort: [Sortlap1]+[Sortlap2]+[Sortlap3]+[Sortlap4]+[Sortlap5]+[Sortlap6]+[Sortlap7]+[Sortlap8]

Then i get the 1 an 0 shown as ie 111000 and not = 3 its not added cumulatively but just put next to each other. Any suggestions please
I use 2007

[Stewart Ross]

I don't want to spoil the party here, but the values you show in the IIF's are text 0s and 1s, not numbers. Text values are indicated by being enclosed in double quotes in an IIF; numeric values are not.

If you try to 'add' text values using the + operator you get string concatenation instead - which is why you are seeing 111000 etc.

To get you out of this fix you could simply take the Val of each item, but my feeling is that your approach is somewhat flawed if you're having to do this sort of thing:

Totalsort: val([Sortlap1])+val([Sortlap2])+val([Sortlap3])... etc

You could of course try returning 1s and 0s as numeric values from your IIF instead:

sortlap1:IIF(Len(Nz([lap1time], "")) > 0, 1, 0)

I'm just doubtful about this kind of approach, which seems expedient in some ways but not systematic or thought through.

I don't know your application the way you do, so really you must make of it what you will and use whatever approach is simplest for you. Try the revised IIF first - it is easier than the Val's.

-Stewart

[neelsfer]

Hi
thx for assistance.
i tried

sortlap1:IIF(Len(Nz([lap1time], "")) > 0, 1, 0)

and

Totalsorting: Val([Sortlap1])+Val([Sortlap2])+Val([Sortlap3])

and i get a total value. My problem now is that i cant do a ascending sort on this total in query.This is what i need to do to sort the times of the athletes.
The screen image below will show what i am trying to achieve with my sorting.
The values encircled is the athletes overall finish position in race based on max of 6 laps. if you do 5 laps then person with shortest time finish in 2nd position etc

[gershwyn]

My problem now is that i cant do a ascending sort on this total in query.

Wouldn't you want to sort in DESCENDING order, considering the more laps you've completed, the higher you should be ranked?

[neelsfer]

thx You are correct. The winner finished the most laps in the shortest time.

[neelsfer]

The bottom line is that the winner does the most laps in the shortest time. I am stuck with this problem and this will be the last report to complete, in my application. Anybody with suggestions?

[neelsfer]

this code provide me with a "1" if there is a value in field or "0" if null

sortlap1: IIf(Len(Nz([lap1time],""))>0,1,0)

i then add up the "1" and "0" values

Totalsorting: Val([Sortlap1])+Val([Sortlap2])+Val([Sortlap3])+Val([Sortlap4])+Val([Sortlap5])+Val([Sortlap6])+Val([Sortlap7])+Val([Sortlap8])

Next i want to get a "1" if rider did the recommended Totllaps and 10 if he/she did less than quantity in totallaps field

SortAll: IIf([Totalsorting]<[Totallaps],"1","10")

I get this "1" or "10" but when i do an ascending sort, i get errors

[neelsfer]

Got it right after 3 days. I used code below to create the "0" or "1" values in a separate columns based on "actual table values" and not the calculated blank "Null" columns. Then added the 0 +1 etc and sorted the values ascending/descending.

Sort1: IIf(Not IsNull([lap1]),"1","0")

[neelsfer]

mr Adezi assisted me again - his solution - the clever way!

Total: ((SELECT Count(*)  FROM  Rt_XCSumReportT WHERE Rt_XCSumReportT.tottime < Rt_XCSumReportT.tottime)+1)

and

LAPPriority: fRetLAPPriority([Lap1Time],[Lap2Time],[Lap3Time],[Lap4Time],[Lap5Time],[Lap6Time],[Lap7Time],[Lap8Time])

i attach the file also