Ranking Scores in a Query

[Steve S]

I have a query that uses 3 tables and 3 Xtab queries to use as record source
for a report. Three of the numeric fields created as output from this query
are:

Fee Key Score
876 10 236.8
876 10 287.0
876 10 123.7
876 13 231.9
876 13 321.5

I need to add a ‘Rank’ field to produce:

Fee Key Score Rank
876 10 236.8 2
876 10 287.0 1
876 10 123.7 3
876 13 231.9 2
876 13 321.5 1

The final result will be that the highest score, within Key, within Fee is
First and so forth. Is this possible to do with a query. I have other
ranking routines where the values being ranked are in a table but I would
like to do this in a query if possible. I just can’t seem to get it to work.

Help is appreciated

 

[Marshall Barton]

I have a query that uses 3 tables and 3 Xtab queries to use as record source
for a report. Three of the numeric fields created as output from this query
are:

Fee Key Score
876 10 236.8
876 10 287.0
876 10 123.7
876 13 231.9
876 13 321.5

I need to add a ‘Rank’ field to produce:

Fee Key Score Rank
876 10 236.8 2
876 10 287.0 1
876 10 123.7 3
876 13 231.9 2
876 13 321.5 1

The final result will be that the highest score, within Key, within Fee is
First and so forth. Is this possible to do with a query. I have other
ranking routines where the values being ranked are in a table but I would
like to do this in a query if possible.

Here's one way:

SELECT Fee, Key, Score,
(SELECT Count(*)
FROM yourquery As X
WHERE X.Key =T.Key
And X.Score <= T.Score) As Rank
FROM yourquery As T

 

[Dale Fye]

This will work as long as all of your Scores for a particular Fee/Key
combination are discrete, otherwise, you will get duplicates and gaps in your
Rank column).

Select Fee, Key, Score, (SELECT COUNT(*) FROM yourQuery Q WHERE Q.Fee =
yourQuery.Fee and Q.Key = yourQuery.Key AND Q.Score <= yourQuery.Score) as
Rank
FROM yourQuery

HTH
Dale

 

[Michel Walsh]

Here is a solution with a join:


SELECT a.fee, a.key, LAST(a.score), COUNT(*) AS rank
FROM yourQuery AS a INNER JOIN yourQuery AS b
ON a.fee = b.fee
AND a.key =b.key
AND a.score <= b.score
GROUP BY a.fee, a.key


If there are ex-equo, they all got the higest rank: 10, 20, 20, 30 will be
respectively ranked 1, 3, 3, 4


SELECT a.fee, a.key, LAST(a.score), 1+ COUNT(*) AS rank
FROM yourQuery AS a LEFT JOIN yourQuery AS b
ON a.fee = b.fee
AND a.key =b.key
AND a.score < b.score
GROUP BY a.fee, a.key


will return 1, 2, 2, 4




Hoping it may help,
Vanderghast, Access MVP

 

[Steve S]

thanks Marshall, Dale, & Michel for the suggestions. Myabe I did not
adequately explain the problem but when I try any of your suggestions I get a
'Circular Reference' error on the name of my query. the SQL for the query is:

SELECT ModelingXTab.[Twirler ID], ModelingXTab.[Pagent ID] AS Fee,
ModelingXTab.AGSortKey AS [Key], Val([MJ1S]) AS VMJ1S, Val(Nz([MJ2S],0)) AS
VMJ2S, Val(Nz([MJ3S],0)) AS VMJ3S, [VMJ1S]+[VMJ2S]+[VMJ3S] AS Score, (SELECT
Count(*) FROM RecapA As Q WHERE Q.Fee=[Fee ID] AND Q.Key=AGSortKey AND
Q.Score <= Score ) AS Rank
FROM ModelingXTab INNER JOIN Fees ON ModelingXTab.[Pagent ID] = Fees.[Fee ID]
ORDER BY ModelingXTab.[Pagent ID], ModelingXTab.AGSortKey;

the name of the query is 'RecapA' and the input is ModelingXTab (a XTab query

In your examples where you have 'yourQuery' I also substituted 'ModelinXTab'
but that produced a parm query asking for Fee, Key,,,

HELP!!!

Another thought - Would it be possible to rank the sums of 3 scores in the
Xtab?

It's SQL is:

TRANSFORM Max(Switch(X.FldName='N', T.Judge,
X.FldName='S', T.Score,
X.FldName='R',T.Rank))
SELECT T.[Pagent ID], T.[Age Group], T.[Twirler ID], T.AGSortKey
FROM tblXtab AS X, tblRecap AS T
WHERE T.[Sub Event] = 1
GROUP BY T.[Pagent ID], T.[Age Group], T.[Twirler ID], T.AGSortKey
PIVOT "MJ" & T.JudSeq & X.FldName
In ('MJ1N',
'MJ1S',
'MJ1R',
'MJ2N',
'MJ2S',
'MJ2R',
'MJ3N',
'MJ3S',
'MJ3R');