Ranking difficulty

[MArk]

Hi All
Have a small problem. I wish to do thh following:

I created a ranking by ranking the scores and named it RANKID.

RankId Name Score
1 JOhn 100
2 Mark 95
3 Greg 90
4 Mike 80

and then i'm calculating the difference between the value above it. like this
1 JOhn 0
2 Mark 5
3 Greg 5
4 Mike 10

I currently;
Select max(score) from Tablex as x where rankid=x!rankid+1 and name=x!name
which works fine
however if i have a tied score like this
1 JOhn 100
2 Mark 90
2 Greg 90
4 Mike 85

and i want the answer like below after i calc the differences to be
1 JOhn 0
2 Mark 10
2 Greg 10
4 Mike 5

problem is i cant use the rankid=rankid+1 as it wont match the rankid for
the fourth ranked.
Ok i could create an if statement.....if error +2..... but sometimes the
scores may have heaps of ties.
any help appreciated.mind boggling...

 

[Ken Snell \(MVP\)]

Perhaps this (I'm using TheName as the field name for your Name field, and
TableName as the name for your table -- If you continue to use Name as the
field name, this query will cease working if you ever switch to Design view
(grid) for the query after you've entered it in SQL view because Jet cannot
parse the [ ] characters that you'd need around Name in the subquery):

SELECT T.RankID, T.TheName,
Val("0" &
((SELECT TOP 1 A.Score
FROM TableName AS A
WHERE A.RankID<T.RankID
ORDER BY A.RankID DESC, A.TheName) - T.Score)) AS ScoreDiff
FROM TableName AS T
ORDER BY T.RankID, T.TheName;


The above query is using a subquery to get the desired Score value from
which you'll subtract the current score value. I am concatenating a 0 to the
front of the value being calculated so that the Null value that results for
the highest score person will be changed to a zero difference, and the Val
function is used to reconvert the string expression (caused by prepending
the 0 character) back to a number.


I noted that you're using Name as the name of a field in a table. It and
many other words are reserved words in ACCESS and should not be used for
control names, field names, etc. See these Knowledge Base articles for more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

 

[John Spencer]

Can you do it this way?

SELECT Name, Score,
Nz(SELECT Min(Tmp.Score)
FROM TheTable as Tmp
WHERE Tmp.Score > TheTable.Score),100) - Score as Difference
FROM TheTable


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[MArk]

no ohn that didnt work that way.
i tried kens way but i get a completely weird answer to what i need.
basically i need a way to split ties. teh names are different so maybe i
could if their scores tie then there names go down to alphabetical and
seperate it.