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

[neelsfer]

I have a query called "Raceresult s1Q" 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 **
[imgnothumb]http://bytes.com/attachments/attachment/4692d1296735346/sortreport1.jpg[/imgnothumb]

[ADezii]

Subscribing, will return...

[neelsfer]

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.

[ADezii]

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 [CategoryPositio n] 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.

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim intOverallPosition As Integer
Dim intNumOfFemales As Integer
Dim intNumOfMales As Integer
Dim intNumOfSeniors As Integer
Dim intNumOfJuniors As Integer

Set MyDB = CurrentDb()
Set rst = MyDB.OpenRecordset("resultsdata", dbOpenDynaset)

With rst
  Do While Not .EOF
    .Edit
      intOverallPosition = intOverallPosition + 1
        If ![gender] = "Female" Then
          intNumOfFemales = intNumOfFemales + 1
          ![GenderPosition] = intNumOfFemales
        Else
          intNumOfMales = intNumOfMales + 1
          ![GenderPosition] = intNumOfMales
        End If
        If ![Category] = "Senior" Then
          intNumOfSeniors = intNumOfSeniors + 1
          ![CategoryPosition] = intNumOfSeniors
        Else
          intNumOfJuniors = intNumOfJuniors + 1
          ![CategoryPosition] = intNumOfJuniors
        End If
          ![OverallPosition] = intOverallPosition
    .Update
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

DoCmd.OpenReport "rptRaceResultsSorted", acViewPreview, , , acWindowNormal

[NeoPa]

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.

[Rabbit]

To rank a field

SELECT UniqueID, FieldToRank, 
   ((SELECT Count(*) FROM Table1
     WHERE Table1.FieldToRank < T1.FieldToRank) + 1) AS Rank
FROM Table1 AS T1

To do ranking by grouping

SELECT UniqueID, FieldToRank, GroupField,
   ((SELECT Count(*) FROM Table1
     WHERE Table1.FieldToRank < T1.FieldToRank
     AND Table1.GroupField = T1.GroupField) + 1) AS Rank
FROM Table1 AS T1

[NeoPa]

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.

[ADezii]

Just knew that the SQL Gang would come to the rescue! (LOL). Please tell me if I am overcomplicatin g 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.

[Rabbit]

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.

[neelsfer]

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 **
[imgnothumb]http://bytes.com/attachments/attachment/4694d1296759389/mtbresults1.jpg[/imgnothumb]

[ADezii]

@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...

[neelsfer]

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.

[Rabbit]

Given a table named Table1 with the following fields and data:

ID Sex AmountSold
1  M   100
2  F   101
3  M   103
4  F   104

The SQL to do an overall rank and a rank by gender would be:

SELECT ID, Sex, AmountSold,
  ((SELECT Count(*) FROM Table1
    WHERE Table1.AmountSold > T1.AmountSold) + 1) AS OverallRank,
  ((SELECT Count(*) FROM Table1
    WHERE Table1.AmountSold > T1.AmountSold
    AND Table1.Sex = T1.Sex) + 1) AS GenderRank
FROM Table1 AS T1;

[neelsfer]

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.

select OD.OrderID, LineNumber, OD.ProductID, UnitPrice, Quantity, Discount 
  from  Northwind.dbo.[Order Details] OD
       join 
        (select count(*) LineNumber, 
                a.OrderID, a.ProductID
                from Northwind.dbo.[Order Details] A join
                     Northwind.dbo.[Order Details] B 
                     on  A.ProductID >= B.ProductID
                         and A.OrderID = B.OrderID
                  group by A.OrderID, A.ProductID) N
          on OD.OrderID= N.OrderID and 
             OD.ProductID = N.ProductID
    where OD.OrderID < 10251
    order by OD.OrderID, OD.ProductID

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

[Rabbit]

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

select OrderID, ProductID, UnitPrice, Quantity, Discount,
  (select count(*)
   from Northwind.dbo.[Order Details] A
   where  A.ProductID >= OD.ProductID 
   and A.OrderID = OD.OrderID) AS LineNumber
from  Northwind.dbo.[Order Details] OD 
where OD.OrderID < 10251 
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.