Rank Within Group Query

[wcrimi]

I have a table with the following Information.

Track
Date of Race
Race Number
Odds

The data looks something like this

BEL; 10/31/2015; 1; 4.50
BEL; 10/31/2015; 1; 2.50
BEL; 10/31/2015; 1; 9.00
BEL; 10/31/2015; 1; 1.50
BEL; 10/31/2015; 2; 6.50
BEL; 10/31/2015; 2; 1.50
BEL; 10/31/2015; 2; 9.00
BEL; 10/31/2015; 2; 8.50
SAR; 10/31/2015; 1; 2.50
SAR; 10/31/2015; 1; 1.50
SAR; 10/31/2015; 1; 9.00
SAR; 10/31/2015; 1; 2.50
SAR; 10/31/2015; 2; 12.50
SAR; 10/31/2015; 2; 11.50
SAR; 10/31/2015; 2; 1.00
SAR; 10/31/2015; 2; 1.50
SAR; 10/31/2015; 3; 0.50
SAR; 10/31/2015; 3; 1.50
SAR; 10/31/2015; 3; 11.00
SAR; 10/31/2015; 3; 11.50
SAR; 11/01/2015; 1; 1.45
SAR; 11/01/2015; 1; 11.45
SAR; 11/01/2015; 1; 9.90
SAR; 11/01/2015; 1; 1.45


I want to group this data on track, date, race, and within that group (5 groups in the data above) I want to have the individual races ranked by odds lowest to highest.

So the first 2 groups would look something like this:

BEL; 10/31/2015; 1; 4.50 3
BEL; 10/31/2015; 1; 2.50 2
BEL; 10/31/2015; 1; 9.00 4
BEL; 10/31/2015; 1; 1.50 1
BEL; 10/31/2015; 2; 6.50 2
BEL; 10/31/2015; 2; 1.50 1


BEL; 10/31/2015; 2; 9.00 4
BEL; 10/31/2015; 2; 8.50 3

I need the rank in some kind of field that I can then use as input elsewhere. The documentation I've found on ranking in Access is practically incomprehensible to me.


Thanks

Wayne

[June7]

Yes, what you want to do is not simple.

You might have to resort to VBA code writing records to a temp table (table is permanent, data is temporary).

[NTC]

one can use a Report object to achieve your goal; set a group on the 3rd field (the 1,2,3 field in your example)

then set a Sort on the fourth field so they are displaying in order smallest to highest

finally set in an unbound row numbering field, set up to number by group not overall

[June7]

But the ranking will not be available for use elsewhere.

So why does it need to be 'input elsewhere'? What are you really trying to achieve?

[orange]

Just curious, if this post is related to one we worked on previously.

[wcrimi]

Just curious, if this post is related to one we worked on previously.

Yes. It's part of the same project I am working on.

I have a very long list of "desires" to study the data I have. I've been slowly knocking things off the list as I learn. On a few occasions I have resorted to exporting a portion of the data to Excel where the manipulation was easier for "me". Then I imported the data back into a new table. But the list of things I find easier to do in Excel is growing. So my manual intervention is starting to grow too large.

[wcrimi]

one can use a Report object to achieve your goal; set a group on the 3rd field (the 1,2,3 field in your example)

then set a Sort on the fourth field so they are displaying in order smallest to highest

finally set in an unbound row numbering field, set up to number by group not overall

You are going to have be patient with my lack of knowledge. I'm still a beginner even though I've been working with Access for close to a year now. I am self educating. I haven't used report objects.

1. I have to group the items on Track, Date, and Race because I need separate ranks for each track/date/race. It can't be just the Race. I know how to create a query that groups.

I tried that.

2. When I set a sort on the 4th field (odds), I get back the message: "your query does not include the specified expression "odds" as part of an aggregate function". That makes perfect sense to me because it wants me to sum, average, min, max, count etc... that odds field. It won't just sort them.

I am not sure how to do what you are telling me to do.

Since I can't even get past the sort step, I have no idea how to do this: "finally set in an unbound row numbering field, set up to number by group not overall"

I can easily sort the table manually so it's in the exact order I want. But I can't add the rank within group.

[orange]

Based on your sample data, I got this (no ranking) result using
query:

SELECT *
FROM (
    SELECT a1.track
        ,a1.raceDate
        ,a1.RaceNumber
        ,a1.odds
    FROM [Racing] AS a1
    INNER JOIN [Racing] AS a2 ON (a1.track = a2.track)
        AND (a1.racedate <= a2.racedate)
        AND (a1.odds <= a2.odds)
    GROUP BY a1.track
        ,a1.racedate
        ,a1.racenumber
        ,a1.odds
    ) AS RankingQuery
ORDER BY RankingQuery.track
    ,RankingQuery.racedate
    ,RankingQuery.racenumber
    ,rankingquery.odds;

track	raceDate	RaceNumber	odds
BEL	31/10/2015	1	1.5
BEL	31/10/2015	1	2.5
BEL	31/10/2015	1	4.5
BEL	31/10/2015	1	9
BEL	31/10/2015	2	1.5
BEL	31/10/2015	2	6.5
BEL	31/10/2015	2	8.5
BEL	31/10/2015	2	9
SAR	31/10/2015	1	1.5
SAR	31/10/2015	1	2.5
SAR	31/10/2015	1	9
SAR	31/10/2015	2	1
SAR	31/10/2015	2	1.5
SAR	31/10/2015	2	11.5
SAR	31/10/2015	2	12.5
SAR	31/10/2015	3	0.5
SAR	31/10/2015	3	1.5
SAR	31/10/2015	3	11
SAR	31/10/2015	3	11.5
SAR	01/11/2015	1	1.45
SAR	01/11/2015	1	9.9
SAR	01/11/2015	1	11.45

Hope it's helpful.

You could put these results in a table, then append a incremental ranking based on
track & date & race.Then rest the ranking to 1 when track & date & race changes.


Post posting comment: Just noticed that I have dropped a record where Odds were equal (2.5)
SAR Oct 31 Race 1??? SAR Nov 1 Race 1 (1.45)

So, must deal with issue when Odds are equal within Group.

[wcrimi]

"You could put these results in a table, then append a incremental ranking based on
track & date & race.Then rest the ranking to 1 when track & date & race changes."

Thanks.

But this is part I don't know how to do. :-)

[orange]

Revised the query to

SELECT *
FROM (
	SELECT a1.track
		,a1.raceDate
		,a1.RaceNumber
		,a1.odds
	FROM [Racing] AS a1
	INNER JOIN [Racing] AS a2 ON (a1.track = a2.track)
		AND (a1.racedate <= a2.racedate)
		AND (a1.odds <= a2.odds)
		AND a1.id <= a2.id
	GROUP BY a1.track
		,a1.racedate
		,a1.racenumber
		,a1.odds
		,a1.id
	) AS RankingQuery
ORDER BY RankingQuery.track
	,RankingQuery.racedate
	,RankingQuery.racenumber
	,rankingquery.odds;

which gives these results:

track	raceDate	RaceNumber	odds
BEL	31/10/2015	1	1.5
BEL	31/10/2015	1	2.5
BEL	31/10/2015	1	4.5
BEL	31/10/2015	1	9
BEL	31/10/2015	2	1.5
BEL	31/10/2015	2	6.5
BEL	31/10/2015	2	8.5
BEL	31/10/2015	2	9
SAR	31/10/2015	1	1.5
SAR	31/10/2015	1	2.5
SAR	31/10/2015	1	2.5
SAR	31/10/2015	1	9
SAR	31/10/2015	2	1
SAR	31/10/2015	2	1.5
SAR	31/10/2015	2	11.5
SAR	31/10/2015	2	12.5
SAR	31/10/2015	3	0.5
SAR	31/10/2015	3	1.5
SAR	31/10/2015	3	11
SAR	31/10/2015	3	11.5
SAR	01/11/2015	1	1.45
SAR	01/11/2015	1	1.45
SAR	01/11/2015	1	9.9
SAR	01/11/2015	1	11.45

[wcrimi]

This seems to be working. I feel like that experiment where if you get a bunch of monkeys to randomly type, eventually one of them will write a novel by accident. I'm THAT monkey. haha

Does this look like it makes sense?



SELECT Tstodds.track, Tstodds.daterace, Tstodds.race, Tstodds.odds, (select Count(*) from oddstable
where odds <= Tstodds.odds
and
track=Tstodds.track
and
daterace = tstodds.daterace
and
race = tstodds.race
) AS rank
FROM Oddstable AS Tstodds
ORDER BY Tstodds.track, Tstodds.daterace, Tstodds.race;

[orange]

Here is the procedure to:
Using your sample data I created a table called Racing.
Then created the query RacingWithOdds

SELECT *
FROM (
    SELECT a1.track
        ,a1.raceDate
        ,a1.RaceNumber
        ,a1.odds
    FROM [Racing] AS a1
    INNER JOIN [Racing] AS a2 ON (a1.track = a2.track)
        AND (a1.racedate <= a2.racedate)
        AND (a1.odds <= a2.odds)
        AND a1.id <= a2.id
    GROUP BY a1.track
        ,a1.racedate
        ,a1.racenumber
        ,a1.odds
        ,a1.id
    ) AS RankingQuery
ORDER BY RankingQuery.track
    ,RankingQuery.racedate
    ,RankingQuery.racenumber
    ,rankingquery.odds;

Then to get a repeatable structure that included Rank, based on ascending Odds within
the group Track, RaceDate, RaceNumber,

Using a Make Table query (CreateRacingWithOddsAndRank), created a new Table based on the RacingWithOdds query. Included a new field "Rank" in the new table which is called RacingWithOddsAndRank.

The query code is:

SELECT *
    ,Rank
INTO RacingWithOddsAndRank
FROM (
    SELECT a1.track
        ,a1.raceDate
        ,a1.RaceNumber
        ,a1.odds
    FROM Racing AS a1
    INNER JOIN Racing AS a2 ON (a1.id <= a2.id)
        AND (a1.odds <= a2.odds)
        AND (a1.racedate <= a2.racedate)
        AND (a1.track = a2.track)
    GROUP BY a1.track
        ,a1.racedate
        ,a1.racenumber
        ,a1.odds
        ,a1.id
    ) AS RankingQuery
ORDER BY RankingQuery.track
    ,RankingQuery.racedate
    ,RankingQuery.racenumber
    ,RankingQuery.odds;

For my purposes I deleted the records that were created during the MakeTable query, and just kept the structure.
Then repopulated the table with the rank value based on the procedure following.

As mentioned above, the new table is called RacingWithOddsAndRank and it is populated with the data from RacingWithOdds and the Rank that was determined in the following procedure. This table has the Rank that can be used in subsequent processes.

'---------------------------------------------------------------------------------------
' Procedure : racingRank
' Author    : mellon
' Date      : 04/11/2015
' Purpose   : Routine to use the RacingWithOdds query and to calculate the rank within
' the Track/RaceDate/RaceNumber groups. Then output the data with Rank to a new table
' called "RacingWithOddsAndRank".
'
' https://www.accessforums.net/queries/rank-within-group-query-56170.html
'---------------------------------------------------------------------------------------
'
Sub racingRank()
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim rs1 As DAO.Recordset
          Dim Hold As String
          Dim Rank As Integer
10        On Error GoTo racingRank_Error

20        Rank = 1
30        Set db = CurrentDb
40        Set rs = db.OpenRecordset("RacingWithOdds")        'based on query
50        Set rs1 = db.OpenRecordset("RacingWithOddsAndRank")    ' create table with a rank field
60        With rs1
70            Do While Not rs.EOF   'Loop through the  "RacingWithOdds" data

                  'check for eof
80                If rs.EOF Then GoTo CloseIT
                  
                  'check if the group has changed
90                If rs!Track & rs!RaceDate & rs!Racenumber = Hold Then    'this is the group that detemines and resets rank
100                   Rank = Rank + 1              'increment Rank
110               Else
120                   Rank = 1                     'reset rank to 1
130                   Hold = rs!Track & rs!RaceDate & rs!Racenumber  ' reset the group to be tested
140               End If
150               Debug.Print rs!Track & " " & rs!RaceDate & " " & rs!Racenumber & "  " & rs!Odds & "  " & Rank  'for debugging

                  'Write the record to RacingWithOddsAndRank
160               .AddNew
170               !Track = rs!Track
180               !RaceDate = rs!RaceDate
190               !Racenumber = rs!Racenumber
200               !Odds = rs!Odds
210               !Rank = Rank  '<-------------------This adds rank to the group
220               .Update       '<-------------------This updates the "RacingWithOddsAndRank" table
230               rs.MoveNext
240           Loop
250       End With
CloseIT:
260       Set rs = Nothing
270       Set rs1 = Nothing
280       db.Close
290       Debug.Print "Finished adding Rank  " & Now()

300       On Error GoTo 0
310       Exit Sub

racingRank_Error:

320       MsgBox "Error " & Err.number & "  at " & Erl & "  (" & Err.Description & ") in procedure racingRank of Module AWF_Related"
End Sub

The data in the RacingWithOddsAndRank table looks like this:

track	raceDate	RaceNumber	odds	Rank
BEL	31/10/2015	1	1.5	1
BEL	31/10/2015	1	2.5	2
BEL	31/10/2015	1	4.5	3
BEL	31/10/2015	1	9	4
BEL	31/10/2015	2	1.5	1
BEL	31/10/2015	2	6.5	2
BEL	31/10/2015	2	8.5	3
BEL	31/10/2015	2	9	4
SAR	31/10/2015	1	1.5	1
SAR	31/10/2015	1	2.5	2
SAR	31/10/2015	1	2.5	3
SAR	31/10/2015	1	9	4
SAR	31/10/2015	2	1	1
SAR	31/10/2015	2	1.5	2
SAR	31/10/2015	2	11.5	3
SAR	31/10/2015	2	12.5	4
SAR	31/10/2015	3	0.5	1
SAR	31/10/2015	3	1.5	2
SAR	31/10/2015	3	11	3
SAR	31/10/2015	3	11.5	4
SAR	01/11/2015	1	1.45	1
SAR	01/11/2015	1	1.45	2
SAR	01/11/2015	1	9.9	3
SAR	01/11/2015	1	11.45	4

I hope this is useful. I did put some comments within the post and the procedure to assist.
Ask, if there are questions.