Adding a Rank column to a report

[OtheymAverian]

Using Access '02, Windows XP Pro SP2

I have a Report that Contains data that is sorted by name and have been asked to add a field that would output a rank based on a numerical (currency) field.

Example Report structure as it is now (dashes added for spacing purposes)

Name-----------Score
----------------------------
ABC------------------10
BCD------------------25
CDE--------------------5

Example Report Structure needed

Rank------Name--------------Score
------------------------------------------
---2---------ABC-------------------10
---1---------BCD-------------------25
---3---------CDE-------------------5

I just started my adventure into Access a few weeks ago and have no SQL knowledge. I have attempted to make a query to pull data, resort, and number, but I have been unable to get my head around some of the comcepts. I do not know if it is possible to add a field into a query that would auto count a rank (based on the query being sorted by the Score field)

[Rabbit]

Are you going to sort by rank on the report?

[OtheymAverian]

No I am not. The report will still continue to be sorted by name. But I need the records ranked as if they were sorted by score.

[OtheymAverian]

Is there any way to do this at all with Access functions, or do I have to lears SQL soon?

[Rabbit]

SELECT Temp.Score,Temp.Name, ((Select Count(*) from TableName Where [Score] > [Temp].[Test];)+1) AS Rank
FROM TableName AS Temp
ORDER BY Temp.Name;

[OtheymAverian]

Thank you for the code, I am attempting to enter it now but have one question. You refer to a [Test] field at the end of the first line. I am unsure what this refers to, when I enter it in I am prompted to enter a value for [Test].

Also I apologize for the double post. I was a little anxious this morning, and thought I may be able to rephrase what I needed better.

[Rabbit]

Sorry. I forgot to rename it. I was testing the SQL using different fields names. [Test] should be [Score] and you'll have to put your Table Name in place of TableName

[OtheymAverian]

I also put my table name in place of the [Temp] tag. Is this correct? I get all 1's when i use [Score] in place of [Test]

[Rabbit]

No, leave the [Temp] tags alone, they're used as an alias. Only change [Test] to [Score] and TableName to your table's name.

[OtheymAverian]

This got the ranking field working almost exactly as I am looking for! The table contains data that many months worth of data. I use the line

WHERE (((Month([Temp].[Date]))=Month(Date()-28)))

to limit the query to the most recent month (working with data that is a month old always).

The line of code creates a ranking for the entire table and then the results are limited to the most recent month, leaving me with weird rankings (6,8,12,17,21, etc.) but I think i just need to base this query off a query that limita the data set prior to the ranking.

-Oth