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)

[OtheymAverian]

I have everything modified so that the Query has the desired output. When I try to display the [Rank] field in my report I get an error message.

The help file lists the error as : Error 3612

[Rabbit]

Multi-level GROUP BY clause is not allowed in a subquery. (Error 3612)
An SQL subquery cannot list more than one field in a GROUP BY clause.

Please post your SQL.

[OtheymAverian]

SELECT Temp.Date, Temp.Dealership , Temp.NewVehInv, Temp.[Total New Cost], Temp.[Total Holdback], Temp.WhsFinLiab , Temp.[Cash in Bank], Temp.ConInTrans , Temp.UsedVehInv , Temp.[Total Used Cost], Temp.Liability, Temp.[Retail New], Temp.[Retail Used], Temp.Wholesale, Temp.S7Score, Temp.Bonus, Temp.SP, Temp.[Entry Order], ((Select Count(*) from [Sales Report MTD] Where [S7Score] > [Temp].[S7Score];)+1) AS Rank
FROM [Sales Report MTD] AS Temp
ORDER BY Temp.Dealership ;



From our previous posts
Name = Dealership
Score = S7Score

[Rabbit]

Try this, make a query that basically pulls everything from that query you just posted and base the report off that. Now, I'm not talking about a copy of the query, rather I'm talking about a query that SELECT * FROM query.

I think the problem is that the query is pulling data from both a table and a query.

[OtheymAverian]

Cannot edit my last reply for some reason.....

The error only shows up when I reference the [Rank] field in my report (in the details). I do not know if this will help you find the problem.

-Oth

[Rabbit]

Cannot edit my last reply for some reason.....

The error only shows up when I reference the [Rank] field in my report (in the details). I do not know if this will help you find the problem.

-Oth

There's a 5 minute cutoff for edits.

Try out what I said in post #14 and let me know if that works.

[OtheymAverian]

If I make a query to pull all data from the previous query I get all 1's in my report.

The rank populates correctly if you just view the datasheet.

-Oth

[Rabbit]

Okay, try making the query a Make Table query and base the report off it.

[OtheymAverian]

That worked perfectly. It kind of sucks to have to take the extra step to generate the table, but I am much further along than I was before I found you guys. Many many thanks.

-Oth

[Rabbit]

I just tested it out and I see no reason why it shouldn't work. I used a more simplistic table with only 2 fields but otherwise the concept is the same.

I had a table with 2 fields.
I made a query based on the table that also adds a rank.
I made a report based on that query.

It all works, is this the same thing you did?

On a side note that may or may not have any bearing on the issue at hand. Don't name fields "Date" or "Name" or any other reserved keywords/properties/functions used by Access, it will confuse the two in certain situations and may confuse anyone else looking at the database.