The fastest way to calculate rank is to run a VBA function. It would run in seconds even if you had tens of thousands of horses with this table structure:
RCTRACK (Text) PrimaryKey
RCDATE (Date/Time) PrimaryKey
Horse (Text) PrimaryKey
xDate (Date/Time) PrimaryKey
rank (number Integer)
I populated the first four fields with your sample data (leaving rank blank). I changed your 4th field "Date" to "xDate" because you should not use reserved words as field names, and I don't know what that date represents. When you implement this, change "xDate" to something descriptive. The VBA code does not use this field (other than two comments). It only uses the primary key which can have any name for "xDate" since it is ranking the data based on the first three fields of the primary key.
I can see from your sample data that each row has a unique combination of RCTRACK, RCDATE, Horse, and "xDate", which is why I created a table with a primary key that consists of those four fields. This is why I have no query at all in the code. The existence of the primary key when going through the rows is effectively the same thing but it is extremely fast.
If you don't already have a module, create one. Copy the VBA code, compile, and save.
Code:
Public Function CalcRank()
'rank each set of RCTRACK, RCDATE and Horse in ascending order of xDate (1,2,3,...)
Dim db As Database
Dim HorseRst As Recordset
Dim CurrentRCTRACK As String
Dim CurrentRCDATE As Date
Dim CurrentHorse As String
Dim cont As Boolean
Dim RankNum As Integer
Set db = CurrentDb()
Set HorseRst = db.OpenRecordset("horse data")
HorseRst.Index = "PrimaryKey" 'RCTRACK, RCDATE, Horse and xDate
CurrentRCTRACK = HorseRst![RCTRACK]
CurrentRCDATE = HorseRst![RCDATE]
CurrentHorse = HorseRst![Horse]
cont = True
RankNum = 1
Do While cont 'loop once for each row in order and reset rank to 1 for each unique combination of RCTRACK, RCDATE and Horse
HorseRst.Edit
HorseRst![rank] = RankNum
HorseRst.Update
HorseRst.MoveNext
If HorseRst.EOF Then 'done
cont = False
Else
If HorseRst![RCTRACK] <> CurrentRCTRACK Or HorseRst![RCDATE] <> CurrentRCDATE Or HorseRst![Horse] <> CurrentHorse Then
CurrentRCTRACK = HorseRst![RCTRACK] 'new data set
CurrentRCDATE = HorseRst![RCDATE]
CurrentHorse = HorseRst![Horse]
RankNum = 1 'start ranking again for the next horse etc.
Else
RankNum = RankNum + 1
End If
End If
Loop
HorseRst.Close
db.Close
MsgBox ("done")
End Function
Then, create a macro with one line:
RunCode CalcRank()
When you want to calculate the rank, run the macro. I included the msgbox "done" at the end just so that you know you did run the macro. It runs so fast that without the msgbox, you might not know you ran it! LOL
If you change any of the data, you will need to run the macro again. The code will not care if there is already a rank populated. It will overwrite whatever is there (blank or not).