Ranking with multiple criterias

[mikesal57]

Date
track
race
saddlecloth
HorseName
mline
classdescriptor
intsurface
dist
Sum
Rank

11/29/2024
AQU
1
1
GEORGIA MAGIC
2.5
S
1
1320
5.402643
472

11/29/2024
AQU
1
1A
GRIFFINS WHARF
2.5
S
1
1320
5.538587
408

11/29/2024
AQU
1
2
CADA DIA MEJOR
30
S
1
1320
4.92079
705

11/29/2024
AQU
1
3
MUNDEN
4
S
1
1320
5.238829
542

11/29/2024
AQU
1
4
MATH TUTOR
4
S
1
1320
6.461421
106

11/29/2024
AQU
1
5
APPLE JAX
2
S
1
1320
5.829801
280

11/29/2024
AQU
1
6
GREY ACE
6
S
1
1320
5.936853
253

11/29/2024
AQU
1
7
SEEKERS HOPE
12
S
1
1320
4.598726
828

11/29/2024
AQU
2
1
DUCHESS OF DESTIN
3
C
1
1760
5.139403
598

11/29/2024
AQU
2
2
LADY LAOBAN
3.5
C
1
1760
5.736889
318

11/29/2024
AQU
2
3
ACCELERINA
10
C
1
1760
5.248045
536

11/29/2024
AQU
2
4
JAYS LOVE
15
C
1
1760
4.200188
945

11/29/2024
AQU
2
5
LEM ME DRINK
6
C
1
1760
5.825428
281

11/29/2024
AQU
2
6
ALLEYS SONG
8
C
1
1760
5.168639
583

11/29/2024
AQU
2
7
BIG HAZEL
8
C
1
1760
5.065784
633

11/29/2024
AQU
2
8
BARONESS BOURBON
2.5
C
1
1760
6.172412
174

11/29/2024
AQU
3
1
DAMAGE
12
M
1
1320
4.820195
744

11/29/2024
AQU
3
2
IM SOLO N IN LOVE
30
M
1
1320
4.261741
922

11/29/2024
AQU
3
3
MONOMOY BEACH
5
M
1
1320
4.900912
718

11/29/2024
AQU
3
4
PAPA PAULIE P
3
M
1
1320
6.267306
152

11/29/2024
AQU
3
5
ONE VISION
2.5
M
1
1320
6.259245
155

11/29/2024
AQU
3
6
BIG DOSSER
20
M
1
1320
4.211754
941

11/29/2024
AQU
3
7
WHISKEY FRENS
2
M
1
1320
4.837425
741

11/29/2024
AQU
3
8
BURNING MUNNY
12
M
1
1320
4.375561
894

Hello all....

Need help with Ranking ...

I need this to Rank by date- track- race- saddlecloth

NOT by full page as it is now..

heres the code:

SELECT VALA.Date, VALA.track, VALA.race, VALA.saddlecloth, VALA.HorseName, VALA.mline, VALA.classdescriptor, VALA.intsurface, VALA.dist, VALA.Sum, (Select Count(*) from VALA as M1 Where [sum] >[VALA].[Sum])+1 AS Rank
FROM VALA
ORDER BY VALA.Date, VALA.track, VALA.race, VALA.saddlecloth;


thanks

[Micron]

NOT by full page as it is now.

Can you explain what that means, or better yet, mock up something in Excel that sorts as you need and copy/paste that?
Access sql can sort but if it can apply ranking I've never seen it done - IIRC.
EDIT - or maybe you mean you want to sort the results by only the Rank field so that 941 is first and 152 comes last?

[mikesal57]

Can you explain what that means, or better yet, mock up something in Excel that sorts as you need and copy/paste that?
Access sql can sort but if it can apply ranking I've never seen it done - IIRC.
EDIT - or maybe you mean you want to sort the results by only the Rank field so that 941 is first and 152 comes last?

Thx but I figured out

[Micron]

It would be nice if you posted your solution. After all, the purpose of the forum is to help you as well as anyone who visits your thread in the future because they have the same or similar problem.

[mikesal57]

It would be nice if you posted your solution. After all, the purpose of the forum is to help you as well as anyone who visits your thread in the future because they have the same or similar problem.

Sure...I had to make a sub-query

if it matters , here you go

Main Query:

SELECT VALA.Date, VALA.track, VALA.race, VALA.saddlecloth, VALA.HorseName, VALA.mline, VALA.classdescriptor, VALA.intsurface, VALA.dist, VALA.Sum, (Select Count(*) from VALA as M1 Where [sum] >[VALA].[Sum] and vala.date=m1.date and vala.track=m1.track and vala.race=m1.race )+1 AS Rank
FROM VALA
ORDER BY VALA.Date, VALA.track, VALA.race, VALA.saddlecloth;

Sub Query:
SELECT StartersToday.Date, StartersToday.track, StartersToday.race, StartersToday.saddlecloth, StartersToday.HorseName, StartersToday.mline, StartersToday.classdescriptor, StartersToday.intsurface, StartersToday.dist, [valf02]*0.004295+[valf07]*-0.01021+[valf08]*0.000348+[valf09]*0.007837+[valf12]*0.135137+[valf15]*0.015308+[valf16]*0.004119+[valf18]*0.013905+[valf19]*0.01322+[valf21]*0.000823+[valf22]*0.022+[valf23]*0.424765+[valf29]*0.184285+[valf31]*0.00118 AS [SUM]
FROM StartersToday;

[davegri]

DOH - why didn't I figure that out from what you said in your first post?