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
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")
13 1383 -
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.
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.
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")
thx its calculating the 0 and 1 correctly now
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
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
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
- 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?
thx You are correct. The winner finished the most laps in the shortest time.
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?
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
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")
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Karen Bailey |
last post by:
Hi,
I am attempting to write a complex query to group sets of data. I have my
query working correctly, but i ran into a problem with sorting.
I want to sort my query by a string field.
Is...
|
by: sriram |
last post by:
Hi,
I have been seing a weird problem with db2look tool in db2 8.2 on
Windows 2000 platform.
When i spool the DDL using db2look, it spools the DDL in the ascending
order of database objects...
|
by: fred_stevens |
last post by:
Hi all you C boffins:
I need to sort a vector of doubles is ascending order. Qsort will
return the sorted vector, but I need a vector of the indices of the
sorted vector, not the actual sorted...
|
by: Joe |
last post by:
Hi,
I have an asp.net script that connects to MS Access database and displays
data in a table. For some reason I am do not know how to display data say in
ascending order of column1.
I have...
|
by: Nick Weisser |
last post by:
Hi there,
I'm not sure how to select the last 3 items in ascending order.
This does the trick in descending order:
select * from user_menu_main
where deleted = 0 and hidden = 0
order by...
|
by: Eric Lilja |
last post by:
Hello, consider the following assignment and my code for it:
/*
Write a program that does the following:
* Integer numbers shall be read from a textfile and stored in a
std::vector.
The name...
|
by: flyaway888 |
last post by:
Hey,
I have a function that compares 2 arrays with 6 numbers in each and outputs the matching numbers. I have written a function that does this but I need to output the numbers in ascending order...
|
by: ankitmathur |
last post by:
Hi,
I'm trying to overcome a situation whereby I have to search through 4-5 columns and produce the results with an order by according to the values matched in these columns.
Example:
My...
|
by: william67 |
last post by:
I'm having a hard time building a query to do what I need to do and was hoping some genius could help me out, I need to do a complex query and any and all help is much appreciated
this is the...
|
by: neelsfer |
last post by:
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...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |