469,271 Members | 997 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Adding a Rank column to a report

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)
Mar 7 '07 #1
39 6518
Rabbit
12,516 Expert Mod 8TB
Are you going to sort by rank on the report?
Mar 7 '07 #2
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.
Mar 7 '07 #3
Is there any way to do this at all with Access functions, or do I have to lears SQL soon?
Mar 8 '07 #4
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. SELECT Temp.Score,Temp.Name, ((Select Count(*) from TableName Where [Score] > [Temp].[Test];)+1) AS Rank
  2. FROM TableName AS Temp
  3. ORDER BY Temp.Name;
Mar 8 '07 #5
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.
Mar 8 '07 #6
Rabbit
12,516 Expert Mod 8TB
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
Mar 8 '07 #7
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]
Mar 8 '07 #8
Rabbit
12,516 Expert Mod 8TB
No, leave the [Temp] tags alone, they're used as an alias. Only change [Test] to [Score] and TableName to your table's name.
Mar 8 '07 #9
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
Mar 8 '07 #10
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
Mar 8 '07 #11
Rabbit
12,516 Expert Mod 8TB
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.
Mar 8 '07 #12
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
Mar 8 '07 #13
Rabbit
12,516 Expert Mod 8TB
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.
Mar 8 '07 #14
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
Mar 8 '07 #15
Rabbit
12,516 Expert Mod 8TB
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.
Mar 8 '07 #16
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
Mar 8 '07 #17
Rabbit
12,516 Expert Mod 8TB
Okay, try making the query a Make Table query and base the report off it.
Mar 8 '07 #18
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
Mar 8 '07 #19
Rabbit
12,516 Expert Mod 8TB
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.
Mar 8 '07 #20
Rabbit
12,516 Expert Mod 8TB
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.
Mar 8 '07 #21
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
Mar 8 '07 #22
NeoPa
32,171 Expert Mod 16PB
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 :)
Mar 9 '07 #23
Rabbit
12,516 Expert Mod 8TB
I found it online somewhere, it was for ascending so had to change it a bit for descending.
Mar 9 '07 #24
NeoPa
32,171 Expert Mod 16PB
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).
Mar 9 '07 #25
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
Mar 9 '07 #26
NeoPa
32,171 Expert Mod 16PB
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.
Mar 9 '07 #27
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
Mar 9 '07 #28
NeoPa
32,171 Expert Mod 16PB
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False)
  2. {Your action query(s) here}
  3. Call DoCmd.SetWarnings(True)
Mar 9 '07 #29
Where do I put that code? I only need it when runnig one specific query.

-Oth
Mar 9 '07 #30
NeoPa
32,171 Expert Mod 16PB
Do you currently have code that executes your action query(s)?
Or do you run them manually?
Mar 9 '07 #31
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
Mar 9 '07 #32
NeoPa
32,171 Expert Mod 16PB
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.
Mar 9 '07 #33
NeoPa
32,171 Expert Mod 16PB
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.
Mar 9 '07 #34
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub SalesNetMTDReport_Click()
  2. On Error GoTo Err_SalesNetMTDReport_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "Sales Net MTD"
  7.     DoCmd.OpenReport stDocName, acPreview
  8.  
  9. Exit_SalesNetMTDReport_Click:
  10.     Exit Sub
  11.  
  12. Err_SalesNetMTDReport_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_SalesNetMTDReport_Click
  15.  
  16. End Sub
Mar 9 '07 #35
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
Mar 9 '07 #36
NeoPa
32,171 Expert Mod 16PB
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.
Mar 9 '07 #37
NeoPa
32,171 Expert Mod 16PB
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 :)
Mar 9 '07 #38
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
Mar 9 '07 #39
NeoPa
32,171 Expert Mod 16PB
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.
Mar 9 '07 #40

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by David Shadovitz | last post: by
2 posts views Thread by Chris Cobb | last post: by
reply views Thread by Nunya Biznas | last post: by
2 posts views Thread by dSchwartz | last post: by
2 posts views Thread by rfdjr1 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.