League Table in Report

[Sparky]

Hi, all.

I want to create a league table report in Access. I'm fairly "au fait" with
Access, but there's something this report needs that I don't know how to do.

I need a column which gives the position in the league of each store:

1 Birmingham 100
2 London 98
3 Bristol 97
3 Cardiff 97
5 Glasgow 85

Notice how Bristol and Cardiff have the same score, and therefore the same
position in the league, and that 4th place is missing altogether? That's
what I want to achieve in my report.

What I've got so far is a incrementing integer value by putting "=1" in the
control source for the textbox, and setting the "Running Sum" property to
"true". This obviously doesn't cater for the requirements.

Of course the report is running off a query, so if the solution requires a
"fix" upstream before getting to the report, then that's no trouble. If VB
code is needed that's no trouble either, 'cos VB is what I know best.

Can anyone help?

Cheers,

<Sparky />

 

[Duane Hookom]

You can do this with a subquery in your report's records source.
SELECT Store, Score, (Select Count(*) From tblScores s where s.Score >
tblScores.Score) +1 as Position
FROM tblScores;