Ranking on Multiple Criteria (1 Viewer)

robsmith

New member
Local time
Today, 16:54
Joined
Feb 17, 2020
Messages
26
Hi,

I've got a table that contains ball by ball cricket data. An extract is attached.

Currently, I import it into Excel and use the following formula (copied down) to find the first delivery a batsman faced in each match, then the second, etc:

=COUNTIFS(BallByBallTable[matchId],A2,BallByBallTable[batsman],E2,BallByBallTable[ballNumber],"<"&C2)+1

The issue is that the table is quite large and Excel is becoming very slow so I want to do it in Access.

So, I want to assign a 1 to the first ball each batsman faced in each match, a 2 to the second and so on.

Can anyone point me in the right direction?

Thanks
 

Attachments

  • Untitled.png
    Untitled.png
    57.3 KB · Views: 77

isladogs

MVP / VIP
Local time
Today, 16:54
Joined
Jan 14, 2017
Messages
18,186
Access has built in support for rank orders in reports.
However its a bit more tricky in queries or forms.
For such purposes you can use subqueries or I prefer to use the Serialize function.
See Rank Order in Queries
 

Users who are viewing this thread

Top Bottom