If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Ranking
I have a query with a member listing. They have all previously been
assigned scores which are integer values ranging from 1-1200 but they are not sesquential and there is not pattern to how they are spaced in value. Field name Score Is there a formula I can use to have the query sort the member ascending by score and then add a new column that is the rank. Rank would need to be assign as 1,2,3,etc with no spaces and all integer values? I really appreciate any help you can offer. Lori |
#2
|
|||
|
|||
Hi,
SELECT a.f1, LAST(a.f2), LAST(a.f3), COUNT(*) As Rank FROM myTable As a INNER JOIN myTable As b ON a.score = b.score GROUP BY a.f1 where f1 is probably the primary key, f2, f3, .. are all the other fields you wish to see. Hoping it may help, Vanderghast, Access MVP "Richardson" wrote in message om... I have a query with a member listing. They have all previously been assigned scores which are integer values ranging from 1-1200 but they are not sesquential and there is not pattern to how they are spaced in value. Field name Score Is there a formula I can use to have the query sort the member ascending by score and then add a new column that is the rank. Rank would need to be assign as 1,2,3,etc with no spaces and all integer values? I really appreciate any help you can offer. Lori |
#3
|
|||
|
|||
Hi,
... use : ON a.score = b.score to get the reverse ordering... evidently. Vanderghast, Access MVP "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... Hi, SELECT a.f1, LAST(a.f2), LAST(a.f3), COUNT(*) As Rank FROM myTable As a INNER JOIN myTable As b ON a.score = b.score GROUP BY a.f1 where f1 is probably the primary key, f2, f3, .. are all the other fields you wish to see. Hoping it may help, Vanderghast, Access MVP "Richardson" wrote in message om... I have a query with a member listing. They have all previously been assigned scores which are integer values ranging from 1-1200 but they are not sesquential and there is not pattern to how they are spaced in value. Field name Score Is there a formula I can use to have the query sort the member ascending by score and then add a new column that is the rank. Rank would need to be assign as 1,2,3,etc with no spaces and all integer values? I really appreciate any help you can offer. Lori |
#4
|
|||
|
|||
Following, I've pasted an answer in microsoft.public.access today that could meet what you are looking for: Hope being helpful Tonín =========================== I need this to assign ranking numbers to people based on performance. I had been using a piece of code which added an autonumber to a newly created table but hadn't been able to get it to work in versions of Access after 97, so this is great, thanks. It also works descending by changing to and will work as a make table query. "Tonín" wrote: If you really, really need it. And if you don't need an "updatable" query, and assuming your "Name" field is unique ... then you can use next query: SELECT YourTable.NameField, YourTable.AddressField, (SELECT Count(YourTable_1.NameField) FROM YourTable AS YourTable_1 WHERE YourTable_1.NameField=YourTable.NameField) AS RecordID FROM YourTable; BTW, I changed Name into NameField and Address into AddressField. Tonín "Don" escribió en el mensaje ... Is there way to use Auto Number in a query? For example query returns Name Address Record ID A 100 1 B 500 2 I want to add a field that keeps count. I hope this makes sense. Thanks |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Q on ranking columns | Smul182 | General Discussion | 1 | August 10th, 2004 07:35 AM |
Ranking Report | nbs | Setting Up & Running Reports | 3 | July 22nd, 2004 02:57 PM |
Ranking query running EXTREMELY slow | Sean G. | Running & Setting Up Queries | 2 | June 23rd, 2004 06:15 PM |
Percent ranking error - please help!!! | Tina | Worksheet Functions | 2 | December 5th, 2003 08:54 PM |