Create a rank field in a report

[millerlite2ky]

I have a report based on a query. The results of the query is sorted by year and then by a field called [Points].

I'd like to take the results of the query and put them in a report (which I have done). The only thing I have left is to have a field in my report which shows the ranking of the [Points] field by year.Example:

1960...............75 points
1960...............73 points
1960...............66 points

I'd like it to look something like this, as an example

1960...........1...75 points
1960...........2...73 points
1960...........3...66 points

Is there a way to do this?

Scott

 

[Andrew Fergus]

Hi, this can be done in the query - have a read of this article - it helped me with a ranking problem.

The statement in the query will look something like this :

Ranking: (Select Count(*) from TableName Where [Points] > [TableAlias].[Points] ; ) + 1

Note : "TableAlias" is an alias for your table called "TableName" per the query statement (but use your actual table name instead) - in the query design view, click on the table in the top half of the query, Click View -> Properties -> Alias -> insert an alias for the table name here. This alias is necessary for the ranking part of the query to work.

HTH, Andrew.

 

[Andrew Fergus]

I have just read your other post (on filtering the report) and the logic in my previous post may not work if you want rankings for all years presented in one query. I had assumed you were showing one year at a time.
A