469,336 Members | 5,156 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

How to use sequential numbers to sort different fields in a Query?

547 512MB
I have a query called "Raceresults1Q" and a report called Raceresults1R. I would like to sort this query based on 3 fields, but in a specific sequence. See attached screen pic for clarity.

In the first screen pic i only have the times it took to finish the race in shortest to longest sequence.

In the 2nd screen pic i have added the overall position in race 1-10,the Category position and the gender position.
My results must be displayed in the Report called Raceresults1R. Its easy to get overall position - use =1 in control source of a textbox.

The other 2 sorting positions are my problems.

i have just reconstructed my db in a mini version, to give you the idea in access also.
Can the results in the top table be displayed in a Report format as shown in the bottom table of screen pic?
please help.

** Edit **

Attached Images
File Type: jpg sortreport1.jpg (119.9 KB, 980 views)
Attached Files
File Type: zip Sorting database 2003.zip (20.1 KB, 134 views)
Feb 3 '11 #1

✓ answered by ADezii

If such is the case, then
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.ID, T1.firstname, T1.surname, T1.Time, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time < T1.Time)+1) 
  2. AS OverallPosition, T1.gender, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.gender = T1.gender)+1)
  3.  AS GenderPosition, T1.Category, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.Category = T1.Category)+1) 
  4. AS CategoryPosition
  5. FROM resultsdata AS T1
  6. ORDER BY T1.Time;
should do the trick.

27 3309
ADezii
8,800 Expert 8TB
Subscribing, will return...
Feb 3 '11 #2
neelsfer
547 512MB
One thing i forgot to mention is that you can have between 10-16 different categories, that are all mixed up depending whether a lot of different ages entered for the race. I used 2 in this example.
Feb 3 '11 #3
ADezii
8,800 Expert 8TB
The bad news is that there is probably an SQL based solution that I ain't got! Sorry, but SQL isn't exactly my area of expertise. Perhaps one of the more qualified Members in this area will point you in the right direction. The good news is that I do have a Code based solution based on the following Logic:
  1. Create a Recordset based on the resultsdata Table.
  2. Dynamically Update the [OverallPosition], [GenderPosition], and [CategoryPosition] Fields based on incrementing Variables dependent on specific Criteria on the Record position, [Gender], and [Category] Fields.
  3. Open a Report whose Record Source is the resultsdata Table that has recently been Updated.
I'll Post the Code as well as a Demo (Attachment) that I used to arrive at a solution for this Thread.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim intOverallPosition As Integer
  4. Dim intNumOfFemales As Integer
  5. Dim intNumOfMales As Integer
  6. Dim intNumOfSeniors As Integer
  7. Dim intNumOfJuniors As Integer
  8.  
  9. Set MyDB = CurrentDb()
  10. Set rst = MyDB.OpenRecordset("resultsdata", dbOpenDynaset)
  11.  
  12. With rst
  13.   Do While Not .EOF
  14.     .Edit
  15.       intOverallPosition = intOverallPosition + 1
  16.         If ![gender] = "Female" Then
  17.           intNumOfFemales = intNumOfFemales + 1
  18.           ![GenderPosition] = intNumOfFemales
  19.         Else
  20.           intNumOfMales = intNumOfMales + 1
  21.           ![GenderPosition] = intNumOfMales
  22.         End If
  23.         If ![Category] = "Senior" Then
  24.           intNumOfSeniors = intNumOfSeniors + 1
  25.           ![CategoryPosition] = intNumOfSeniors
  26.         Else
  27.           intNumOfJuniors = intNumOfJuniors + 1
  28.           ![CategoryPosition] = intNumOfJuniors
  29.         End If
  30.           ![OverallPosition] = intOverallPosition
  31.     .Update
  32.       .MoveNext
  33.   Loop
  34. End With
  35.  
  36. rst.Close
  37. Set rst = Nothing
  38.  
  39. DoCmd.OpenReport "rptRaceResultsSorted", acViewPreview, , , acWindowNormal
Attached Files
File Type: zip Sorting Database 2003_2.zip (27.8 KB, 92 views)
Feb 3 '11 #4
NeoPa
32,182 Expert Mod 16PB
Reports and queries are indeed different animals. If a report has no sorting or grouping specified it probably defaults to the order specified by the query, but generally a report will handle its own sorting and grouping separately. In the design view of a report there is a Sorting and Grouping button on the Report Design toolbar. Is that not all you need for this?

BTW. You could also manage this in the query if required. It's simply a matter of specifying the criteria you require, which can either be done in SQL, or the query design grid, whichever is more comfortable for you.
Feb 3 '11 #5
Rabbit
12,516 Expert Mod 8TB
To rank a field
Expand|Select|Wrap|Line Numbers
  1. SELECT UniqueID, FieldToRank, 
  2.    ((SELECT Count(*) FROM Table1
  3.      WHERE Table1.FieldToRank < T1.FieldToRank) + 1) AS Rank
  4. FROM Table1 AS T1
To do ranking by grouping
Expand|Select|Wrap|Line Numbers
  1. SELECT UniqueID, FieldToRank, GroupField,
  2.    ((SELECT Count(*) FROM Table1
  3.      WHERE Table1.FieldToRank < T1.FieldToRank
  4.      AND Table1.GroupField = T1.GroupField) + 1) AS Rank
  5. FROM Table1 AS T1
Feb 3 '11 #6
NeoPa
32,182 Expert Mod 16PB
That's clever SQL from Rabbit (and likely to prove very useful over time if you use SQL). An alternative, for reports only, is to have a control on your report which has a value of 1, but has the property .RunningSum set to True. It's certainly true that Rabbit's suggestion is more elegant though.
Feb 3 '11 #7
ADezii
8,800 Expert 8TB
Just knew that the SQL Gang would come to the rescue! (LOL). Please tell me if I am overcomplicating the issue, but aren't we talking about Three, Independent, Rankings based solely on Time Ascending (Post #1), to be incorporated into a single Query? I am referring to Overall Ranking, Gender Ranking, and Category Ranking via Time Ascending. The posted SQL would not cover all three of these conditions, would it? For my own experience, what would the actual SQL Statement be to affect all Rankings? Thanks guys.
Feb 3 '11 #8
Rabbit
12,516 Expert Mod 8TB
It would cover all 3. You would use 3 subqueries. The overall ranking would use the subquery from the first example. And the gender and category ranking would use two copies of the subquery from the second example differing by the grouping field.
Feb 3 '11 #9
neelsfer
547 512MB
thank you to you all. Sql - is very painful to me as a novice but thx.. This seems rather complicated to me.

Mr Adezi - from your solution - it seems i should append all the records after the race into a new table and then generate the report from there with the different groupings such as category and gender. The names of the 10 or so categories that we use are fixed and is always used, so i will add them to the code.
Its a pity there is no "query" method to simplify this issue, so that it changes the results in real time, if one fixes an error after wards.
This screen pic attached is from somebody else doing timing like me, and i would like to achieve that eventually.

** Edit **

Attached Images
File Type: jpg mtbresults1.jpg (98.2 KB, 729 views)
Feb 3 '11 #10
ADezii
8,800 Expert 8TB
@neelsfer - Based on Rabbit's advice, I'll continue to attempt a purely SQL solution to this problem in my spare time. I honestly must tell you that I am not at either Rabbit's or NeoPa's skill-set when it comes to SQL, so do not be disappointed if an immediate solution is not forthcoming. You and I have come a long way with this Project, and I am not about to give up now! (LOL). In the meantime, my alternative Code based solution should work quite well. As far as Real Time Analysis, the Code would simply have to be executed at any Change, Addition, Deletion, etc...
Feb 3 '11 #11
neelsfer
547 512MB
Thx mr Adezi. As you correctly say we have come along way. I can do without that ranking, but it would be a very "nice to have".
Currently i have 3 different reports to provide the same results, as this one report would do.
Feb 3 '11 #12
Rabbit
12,516 Expert Mod 8TB
Given a table named Table1 with the following fields and data:
Expand|Select|Wrap|Line Numbers
  1. ID Sex AmountSold
  2. 1  M   100
  3. 2  F   101
  4. 3  M   103
  5. 4  F   104
The SQL to do an overall rank and a rank by gender would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT ID, Sex, AmountSold,
  2.   ((SELECT Count(*) FROM Table1
  3.     WHERE Table1.AmountSold > T1.AmountSold) + 1) AS OverallRank,
  4.   ((SELECT Count(*) FROM Table1
  5.     WHERE Table1.AmountSold > T1.AmountSold
  6.     AND Table1.Sex = T1.Sex) + 1) AS GenderRank
  7. FROM Table1 AS T1;
Feb 3 '11 #13
neelsfer
547 512MB
i came accross this example just now. maybe one can adapt it

Sequentially Numbering Groups of Records

Another case I have run across for sequentially number records is where you want to number groups of records. Where each group starts numbering from 1 to N, where N is the number of records in the group, and then starts over again from 1, when the next group is encountered.

For an example of what I am talking about, let's say you have a set of order detail records for different orders, where you want to associate a line number with each order detailed record. The line number will range from 1 to N, where N is the number of order detail records per order. The following code produces line numbers for orders in the Northwind Order Detail table.
Expand|Select|Wrap|Line Numbers
  1. select OD.OrderID, LineNumber, OD.ProductID, UnitPrice, Quantity, Discount 
  2.   from  Northwind.dbo.[Order Details] OD
  3.        join 
  4.         (select count(*) LineNumber, 
  5.                 a.OrderID, a.ProductID
  6.                 from Northwind.dbo.[Order Details] A join
  7.                      Northwind.dbo.[Order Details] B 
  8.                      on  A.ProductID >= B.ProductID
  9.                          and A.OrderID = B.OrderID
  10.                   group by A.OrderID, A.ProductID) N
  11.           on OD.OrderID= N.OrderID and 
  12.              OD.ProductID = N.ProductID
  13.     where OD.OrderID < 10251
  14.     order by OD.OrderID, OD.ProductID
  15.  
This code is similar to the prior self join example, except this code calculates the LineNumber as part of a subquery. This way the LineNumber calculated in the subquery can be joined with the complete Order Detail record.

it produces this
OrderID LineNumber ProductID UnitPrice Quantity Discount
----------- ----------- ----------- --------------------- -------- ---------------
10248 1 11 14.0000 12 0.0
10248 2 42 9.8000 10 0.0
10248 3 72 34.8000 5 0.0
10249 1 14 18.6000 9 0.0
10249 2 51 42.4000 40 0.0
10250 1 41 7.7000 10 0.0
10250 2 51 42.4000 35 0.15000001
10250 3 65 16.8000 15 0.15000001
Feb 3 '11 #14
Rabbit
12,516 Expert Mod 8TB
The end result would be the same. There's nothing in my example that precludes you from including the rest of the fields from the record. That query can be rewritten as
Expand|Select|Wrap|Line Numbers
  1. select OrderID, ProductID, UnitPrice, Quantity, Discount,
  2.   (select count(*)
  3.    from Northwind.dbo.[Order Details] A
  4.    where  A.ProductID >= OD.ProductID 
  5.    and A.OrderID = OD.OrderID) AS LineNumber
  6. from  Northwind.dbo.[Order Details] OD 
  7. where OD.OrderID < 10251 
  8. order by OD.OrderID, OD.ProductID
The difference between the two is that my method would use 3 subqueries that select on one table while the other method would use 3 subqueries that cross join 2 tables and then inner join back to the main query. One of them would run quicker but I don't know which one that would be until I actually tried it.
Feb 3 '11 #15
ADezii
8,800 Expert 8TB
Thanks to Rabbit, I am very, very, close to a solution. I'll post the SQL and an Attachment. The problem with Ranking occurs when Times are exactly the same, as indicated by the Rankings for 8:05:00 PM in the Attached Database. If no 2 Times are exactly the same, the SQL works perfectly.
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.firstname, T1.surname, T1.Time, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time < T1.Time)+1) 
  2. AS OverallPosition, 
  3. T1.gender, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.gender = T1.gender)+1) 
  4. AS GenderPosition, 
  5. Category, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.Category = T1.Category)+1) 
  6. AS CategoryPosition
  7. FROM resultsdata AS T1;
P.S. - Surprised I got this far, going to bed and will try to figure out the last remaining detail tomorrow, unless one of you guys beats me to the punch.
Attached Files
File Type: zip Sorting Database 2003_3.zip (31.4 KB, 65 views)
Feb 4 '11 #16
Jerry Winston
145 Expert 100+
You can usually make use of RANK BY to generate these kinds of sequential numbers.

Try this:
Expand|Select|Wrap|Line Numbers
  1. DECLARE @resultsdata TABLE(
  2.     [ID] [int] NOT NULL,
  3.     [firstname] [nvarchar](255) NULL,
  4.     [surname] [nvarchar](255) NULL,
  5.     [Time] [nvarchar](255) NULL,
  6.     [OverallPosition] [nvarchar](255) NULL,
  7.     [gender] [nvarchar](255) NULL,
  8.     [GenderPosition] [nvarchar](255) NULL,
  9.     [Category] [nvarchar](255) NULL,
  10.     [CategoryPosition] [nvarchar](255) NULL
  11. )
  12.  
  13. INSERT INTO @resultsdata VALUES (1,'Anny','August','17:16',NULL,'female',NULL,'Senior',NULL)
  14. INSERT INTO @resultsdata VALUES (2,'John','Wrong','18:06',NULL,'male',NULL,'Senior',NULL)
  15. INSERT INTO @resultsdata VALUES (3,'John','White','18:25',NULL,'Male',NULL,'Senior',NULL)
  16. INSERT INTO @resultsdata VALUES (4,'Susan','Black','19:08',NULL,'Female',NULL,'senior',NULL)
  17. INSERT INTO @resultsdata VALUES (5,'John','July','19:21',NULL,'male',NULL,'senior',NULL)
  18. INSERT INTO @resultsdata VALUES (6,'John','September','19:56',NULL,'male',NULL,'junior',NULL)
  19. INSERT INTO @resultsdata VALUES (7,'Peter','Right','20:05',NULL,'male',NULL,'Junior',NULL)
  20. INSERT INTO @resultsdata VALUES (8,'Peter','Water','20:05',NULL,'male',NULL,'junior',NULL)
  21. INSERT INTO @resultsdata VALUES (9,'Susan','April','21:45',NULL,'Female',NULL,'junior',NULL)
  22. INSERT INTO @resultsdata VALUES (10,'Diane','Coke','21:56',NULL,'female',NULL,'junior',NULL)
  23.  
  24.  
  25.  
  26. SELECT ID,firstname,surname,[Time],
  27. rank() over (partition by 'x' order by [Time])[OverallPosition], 
  28. gender,
  29. rank() OVER (PARTITION BY gender order by [Time]) [GenderPosition],
  30. category,
  31. rank() over (partition by Category order by [Time]) [CategoryPosition]
  32. FROM @resultsdata
  33. ORDER BY [Time] ASC
Sorry this code is for SQL Server!
Feb 4 '11 #17
ADezii
8,800 Expert 8TB
@Rabbit - I am really pulling my hair out on this one, and would desperately appreciate you assistance. This scenario is particularly funny, since I am bald! For the life of me, I cannot figure how to break a Tie for 'exact' Time Matches, and adjust Rankings accordingly. Thanks to your advice, I have gotten this far, but now I am up against a brick wall. The OP and I have a lot of time invested in this Project, and would hate to have it abruptly end here. Kindly download the Attachment and refer to IDs 7 and 8. As always, thanks.
Attached Files
File Type: zip Sorting Database 2003_4.zip (29.8 KB, 91 views)
Feb 4 '11 #18
Jerry Winston
145 Expert 100+
@ADezii

Do you have a rule that explains why one runner should be ranked above another if they have the same time?

In any case this might be your solution (look at the overallPosition field):
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.firstname, T1.surname, T1.Time, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time < T1.Time OR (resultsdata.ID=T1.ID OR resultsdata.ID<T1.ID) )) 
  2. AS OverallPosition, 
  3. T1.gender, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.gender = T1.gender)+1) 
  4. AS GenderPosition, 
  5. Category, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.Category = T1.Category)+1) 
  6. AS CategoryPosition
  7. FROM resultsdata AS T1;
  8.  
  9.  
Feb 4 '11 #19
ADezii
8,800 Expert 8TB
I actually tried similar Logic with erroneous Results as listed below. I restricted the Output to only Time and Overall Position:
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.Time, ((SELECT Count(*)  FROM  resultsdata
  2.  WHERE resultsdata.Time < T1.Time OR (resultsdata.ID=T1.ID OR resultsdata.ID<T1.ID) ))
  3.  AS OverallPosition
  4. FROM resultsdata AS T1
  5. ORDER BY T1.Time;
Query OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Time              OverallPosition
  2. 5 :16:00 PM            1
  3. 6 :06:00 PM            2
  4. 6 :25:00 PM            3
  5. 6 :30:03 PM           11
  6. 7 :08:00 PM            5
  7. 7 :17:02 PM           13
  8. 7 :21:00 PM            7
  9. 7 :56:00 PM            8
  10. 8 :05:00 PM           10
  11. 8 :05:00 PM            9
  12. 9 :45:00 PM           11
  13. 9 :55:55 PM           13
  14. 9 :56:00 PM           13
  15.  
Results using current Logic, excluding First and Last Names. Notice the equal Ranking for match on Time Slot of 8:05:00 PM for all Position Ranks(3) - Lines 10 and 11.
Expand|Select|Wrap|Line Numbers
  1. Time    OverallPosition    gender    GenderPosition    Category    CategoryPosition
  2. 5 :16:00 PM    1           Female          1            Senior          1
  3. 6 :06:00 PM    2           Male            1            Senior          2
  4. 6 :25:00 PM    3           Male            2            Senior          3
  5. 6 :30:03 PM    4           Male            3            Senior          4
  6. 7 :08:00 PM    5           Female          2            Senior          5
  7. 7 :17:02 PM    6           Male            4            Junior          1
  8. 7 :21:00 PM    7           Male            5            Senior          6
  9. 7 :56:00 PM    8           Male            6            Junior          2
  10. 8 :05:00 PM    9           Male            7            Junior          3
  11. 8 :05:00 PM    9           Male            7            Junior          3
  12. 9 :45:00 PM    11          Female          3            Junior          5
  13. 9 :55:55 PM    12          Female          4            Junior          6
  14. 9 :56:00 PM    13          Female          5            Junior          7
  15.  
Feb 4 '11 #20
Jerry Winston
145 Expert 100+
There are only two scenarios in which we want to COUNT the records "above" the current row for T1. When the time is less than the current row. and when the time is equal to the current row but the ID is less than T1 row.



Here's the corrected code:

Expand|Select|Wrap|Line Numbers
  1. SELECT T1.Time, ((SELECT Count(*)  FROM  resultsdata
  2.  WHERE resultsdata.Time < T1.Time OR (resultsdata.Time=T1.Time AND resultsdata.ID < T1.ID) )+1)
  3.  AS OverallPosition
  4. FROM resultsdata AS T1
  5. ORDER BY T1.Time;
  6.  
  7.  
Feb 4 '11 #21
Rabbit
12,516 Expert Mod 8TB
If they tie, shouldn't they have the same rank? Won't the runners be angry if they're arbitrarily ranked lower than someone they tied with?
Feb 4 '11 #22
Jerry Winston
145 Expert 100+
@Rabbit

I agree. (That's why I asked about tie-break rules)
But it doesn't seem to be part of ADezii's requirement.
Feb 4 '11 #23
ADezii
8,800 Expert 8TB
@Jerry and Rabbit: First of all, thanks a million for your help on this puzzling matter. As far as Ties go, this question should actually be directed to the Original Poster of this Thread, namely: neelsfer. He must define exactly what the Tie Breaker is in the event of equal Time Finishes, and this Tie Breaker must be Unique as I see it (cannot be a Race Registration Date). I know that we were trying to incorporate Milliseconds into the Timings in a Related Thread, probably for this exact scenario, but this is not that simple as Milliseconds are not integrated into Access Date/Time Fields. I'm too deeply into this, which is why I need some outside opinions. Do either one of you disagree with anything that was previously stated in this Post?

@neelsfer - Need you intervention at this point on the matter of Ties.
Feb 4 '11 #24
Jerry Winston
145 Expert 100+
@ADezii

Sorry, I did definitely pointed the tie-beak question at you instead of OP.
Feb 4 '11 #25
ADezii
8,800 Expert 8TB
Not a problem, Jerry. The OP and I are virtually one and the same at this point! (LOL). I was actually thinking of making a [Milliseconds] Field {INTEGER} as the Tie Breaker. This Field would exist independently of the [Time] Field as a matter of necessity. The odds of 2 runners finishing at exactly the same Hour, Minute, Second, and Millisecond would be extremely small, but again we'll just have to wait for the OP. Thanks again.
Feb 4 '11 #26
neelsfer
547 512MB
Thxs guys. If i time a mountain biking event, then the chances are almost zero to have a tie as we capture the racenumbers as they pass the finish line.

The problem comes in with Road cycling (thats now what Mr "drugfree" Armstrong does).
They make use of what is called a "bunch or group time". If a group of riders all finish TOGETHER, they are all given the same finish time, but we manually identify the first 3 riders usually for prizes.

It could sometimes be 5-20 riders. The finish sequence is then 1,2,3,4,5,6...10, as we stop them in a "shoot" in their group finish sequence, but the same time is given to everybody then.
I use a button in my program that saves that bunch time in a textbox, and then i transfer that finish time to where the subform data is captured, and i just add the racenumbers afterwards.

In downhill mountain biking ( is going like a maniac down a mountain with no brakes) the riders start and finish on their own, and you may sometimes have a tie in seconds. This is where milliseconds may help.
Almost 90% of the races i do is normal mountainbiking, so lets rather assume there are no two riders with the same time in this report..
Hope that helps
Feb 4 '11 #27
ADezii
8,800 Expert 8TB
If such is the case, then
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.ID, T1.firstname, T1.surname, T1.Time, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time < T1.Time)+1) 
  2. AS OverallPosition, T1.gender, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.gender = T1.gender)+1)
  3.  AS GenderPosition, T1.Category, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.Category = T1.Category)+1) 
  4. AS CategoryPosition
  5. FROM resultsdata AS T1
  6. ORDER BY T1.Time;
should do the trick.
Feb 4 '11 #28

Post your reply

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

Similar topics

2 posts views Thread by Tony Williams | last post: by
3 posts views Thread by shaqattack1992-newsgroups | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.