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

[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.

[Rabbit]

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

Not a problem, good luck.

Yes, the extra step is a hassle but if you're going to make it user friendly and have a switchboard, you can basically automate everything through code.

[OtheymAverian]

All part of the learning process I guess. This is the first database that I have designed myself. Thanks for the tips on the Name and Date field names. I have a lot to learn about naming structures.

-Oth

[NeoPa]

All part of the learning process I guess. This is the first database that I have designed myself. Thanks for the tips on the Name and Date field names. I have a lot to learn about naming structures.

-Oth

Try to avoid ever using spaces or punctuation characters in names too.
On the ranking issue, other than the fact that this obviously works, I would have sworn that a subquery couldn't reference a recordset name (Temp in this case) defined outside of the subquery itself. I tried it myself and it works perfectly - nice one.
Thank you Rabbit for this lesson :)

[Rabbit]

I found it online somewhere, it was for ascending so had to change it a bit for descending.

[NeoPa]

I've always found a reference to a dataset (FROM clause) in the outer query cannot be used within the subquery. I suspect that this was due to my generally placing my subqueries within the FROM clause of the outer query though. It's very interesting that subqueries in the SELECT and WHERE clauses can run through (with different data) every time they are used (I mean for each record of the outer query).

[OtheymAverian]

If I were to change the field names in my tables now, after making many queries and reports, would that potentially mess things up?

I am looking to remove the spaces from my field names and rename the [Date] field to something more appropriate.

-Oth

[NeoPa]

If I were to change the field names in my tables now, after making many queries and reports, would that potentially mess things up?

Yes it could.
I would certainly make a mental note to create new ones in future which match those criteria.
As for amending existing names, that's up to you. For me it would depend on how much of a problem would be caused by the knock-on effect.

[OtheymAverian]

Is there a way to disable or auto approve the prompts generated by running a make table query? I have been looking through the posts for something, but I do not know what exacty to search for.

-Oth

[NeoPa]

Call DoCmd.SetWarnings(False)
{Your action query(s) here}
Call DoCmd.SetWarnings(True)

[OtheymAverian]

Where do I put that code? I only need it when runnig one specific query.

-Oth

[NeoPa]

Do you currently have code that executes your action query(s)?
Or do you run them manually?

[OtheymAverian]

I was running them manually until earlier today. Today I made a form that had buttons that initiate the reports.

The query in question is ran when a specific report is opened. (On Open event)

-Oth

[NeoPa]

If you post the code for your OnOpen event I'll show you where to put the extra lines.
Don't forget to use [code] tags.

[NeoPa]

Actually, post the code for the button that opens the report as well as the OnOpen event procedure of the report and we'll have a look at what's going on exactly.

[OtheymAverian]

Are you talking VBA code? I am using the On Open field in the properties box when viewing the report in design mode (selecting report from the drop down box). As far as I can see there is not VBA code for the report.

I think this is what you want for the button :

Private Sub SalesNetMTDReport_Click()
On Error GoTo Err_SalesNetMTDReport_Click
 
    Dim stDocName As String
 
    stDocName = "Sales Net MTD"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_SalesNetMTDReport_Click:
    Exit Sub
 
Err_SalesNetMTDReport_Click:
    MsgBox Err.Description
    Resume Exit_SalesNetMTDReport_Click
 
End Sub

[OtheymAverian]

I put the 2 lines of code in before and after the report was called and it ran without posting the warnings. I think I got it.

-Oth

[NeoPa]

Thanks for the VBA routine posted. That is pretty standard so no problems there.
I don't really understand what you're saying about the OnOpen event procedure of the report so let's try this instead :

1. From the main database window select Reports.
2. Select the report called [Sales Net MTD].
3. Open it for Design.
4. Make sure the Properties pane is open (Alt-Enter).
5. Go down to the 'On Open' property.
6. Select [Event Procedure].
7. Click on the elipsis button (...).
8. When the VBA code window opens just Copy (Ctrl-C) and Paste (Ctrl-V) the whole lot into a post here.

[NeoPa]

I put the 2 lines of code in before and after the report was called and it ran without posting the warnings. I think I got it.

-Oth

That may work sometimes but is not the correct place.
Post me the other code and I'll show you the proper place :)

[OtheymAverian]

OH!! I wrote a tiny little macro that called the query and then called that macro in the 'On Open'. If i view the code for the report there is none. I assume it is because I am calling a macro and not a query directly.

-Oth

[NeoPa]

In that case you have two options :

1. Leave the code as you already have it.
2. Explore in the macro options for the command, for a setting which disables those messages

I would recommend using just VBA code in a database. Mixing the two means that you need to understand both. There's nothing I'm aware of that can be done in a macro but not in code.