467,119 Members | 1,180 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,119 developers. It's quick & easy.

Sorting a complex query in ascending order

512MB
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
Expand|Select|Wrap|Line Numbers
  1. Sortlap1: IIf(Not IsNull([lap1time]),"1","0")
please help
Attached Files
File Type: zip xc results.zip (107.8 KB, 70 views)
File Type: zip SortingReport 20031.zip (72.1 KB, 50 views)
Apr 6 '11 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. IIF(Len(Nz([lap1time], "")) > 0, "1", "0")

  • viewed: 1106
Share:
13 Replies
100+
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT RacerID, Count(LapNumber) as LapsCompleted, Sum(LapTime) as TotalTime FROM RaceStats
  2. WHERE LapTime Is Not Null
  3. 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.
Apr 6 '11 #2
512MB
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.
Apr 6 '11 #3
100+
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:
Expand|Select|Wrap|Line Numbers
  1. IIF(Len(Nz([lap1time], "")) > 0, "1", "0")
Apr 7 '11 #4
512MB
thx its calculating the 0 and 1 correctly now
Apr 7 '11 #5
512MB
When i add up the cumulative values of these fields using
ie
Expand|Select|Wrap|Line Numbers
  1. sortlap1:IIF(Len(Nz([lap1time], "")) > 0, "1", "0")
up to sortlap8

Expand|Select|Wrap|Line Numbers
  1. 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
Apr 7 '11 #6
Expert Mod 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. Totalsort: val([Sortlap1])+val([Sortlap2])+val([Sortlap3])... etc
You could of course try returning 1s and 0s as numeric values from your IIF instead:

Expand|Select|Wrap|Line Numbers
  1. 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
Apr 7 '11 #7
512MB
Hi
thx for assistance.
i tried
Expand|Select|Wrap|Line Numbers
  1. sortlap1:IIF(Len(Nz([lap1time], "")) > 0, 1, 0)
and
Expand|Select|Wrap|Line Numbers
  1. 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
Attached Images
File Type: jpg xc results.jpg (98.7 KB, 87 views)
Apr 7 '11 #8
100+
Expand|Select|Wrap|Line Numbers
  1. 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?
Apr 7 '11 #9
512MB
thx You are correct. The winner finished the most laps in the shortest time.
Apr 7 '11 #10
512MB
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?
Apr 8 '11 #11
512MB
this code provide me with a "1" if there is a value in field or "0" if null
Expand|Select|Wrap|Line Numbers
  1. sortlap1: IIf(Len(Nz([lap1time],""))>0,1,0)
i then add up the "1" and "0" values
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. SortAll: IIf([Totalsorting]<[Totallaps],"1","10")
I get this "1" or "10" but when i do an ascending sort, i get errors
Apr 8 '11 #12
512MB
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.
Expand|Select|Wrap|Line Numbers
  1. Sort1: IIf(Not IsNull([lap1]),"1","0")
Apr 9 '11 #13
512MB
mr Adezi assisted me again - his solution - the clever way!
Expand|Select|Wrap|Line Numbers
  1. Total: ((SELECT Count(*)  FROM  Rt_XCSumReportT WHERE Rt_XCSumReportT.tottime < Rt_XCSumReportT.tottime)+1)
and
Expand|Select|Wrap|Line Numbers
  1. LAPPriority: fRetLAPPriority([Lap1Time],[Lap2Time],[Lap3Time],[Lap4Time],[Lap5Time],[Lap6Time],[Lap7Time],[Lap8Time])
i attach the file also
Attached Files
File Type: zip Sorting XC.zip (66.9 KB, 51 views)
Apr 11 '11 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Karen Bailey | last post: by
2 posts views Thread by fred_stevens@hotmail.com | last post: by
5 posts views Thread by Nick Weisser | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.