Allocating a points value to a score 1

[bxgti4x4]

I have a query that lists "SumOfLngPracticeScore" and "CountOfLngCompetitorNo" which gives me a list of scores (in descending order) and the number of competitors that have achieved that score.

I need to allocate points to each score based on the number of competitors achieving it. e.g If the highest score is 150 the points allocated are 100. Each competitor scoring 150 receives 100 points. If the next highest score is 149 then the points allocated are 99 and so on down the list.

However, the problem does not stop there. If two competitors are awarded 100 points for equal highest score, the points allocated for the next highest score would be 98, not 99. Similarly if three competitors are awarded 98 points for the second highest score, the points for the third highest score would start at 95 and so on.

Any advice would be much appreciated

Best Regards
John Draper

 

[r937]

here's a script which demonstrates a ranking query

(note you must feed Access each statement, including every INSERT, one at a time)

create table t
( id counter not null 
, constraint pkey primary key (id)
, person varchar(11)
, score integer
)
insert into t(person,score) values ('curly',65)
insert into t(person,score) values ('larry',55)
insert into t(person,score) values ('moe',75)
insert into t(person,score) values ('tom',55)
insert into t(person,score) values ('dick',70)
insert into t(person,score) values ('hairy',60)
insert into t(person,score) values ('eenie',70)
insert into t(person,score) values ('meenie',60)
insert into t(person,score) values ('minie',56)
insert into t(person,score) values ('frick',45)
insert into t(person,score) values ('frack',70)

select A.person
     , A.score
     , count(B.score)+1  as rank
     , 100-count(B.score) as points
  from t AS A 
left outer 
  join t AS B 
    on A.score < B.score
group 
    by A.person
     , A.score
order 
    by A.score desc

person score rank points
moe     75     1   100
dick    70     2    99
eenie   70     2    99
frack   70     2    99
curly   65     5    96
hairy   60     6    95
meenie  60     6    95
minie   56     8    93
larry   55     9    92
tom     55     9    92
frick   45    11    90

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[bxgti4x4]

Thanks R937

What I stupidly did not explain was that I already had a table which contains the score for each competitor in each competition. From this I ceated a query which listed the individual totals for each competition. This query is called "QryWhiteheadInd"

I tried to use your script in a query based on the query above as follows:

SELECT A.CompetitorNo, A.SumOfLngPracticeScore, count(B.SumOfLngPracticeScore)+1 AS Rank, 100-count(B.SumOfLngPracticeScore) AS points
FROM QryWhiteheadInd AS A LEFT JOIN QryWhiteheadInd AS B ON A.score<B.score
GROUP BY A.CompetitorNo, A.SumOfLngPracticeScore
ORDER BY A.SumofLngPracticeScore DESC;

Problem - when I run this query, it asks me for the parameters - A.CompetitorNo and A.SumofLngPracticeScore

I am not very good with SQL code so would be grateful if you could tell me where I am going wrong

Best Regards
John

 

[r937]

please show me your query

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[bxgti4x4]

r937

QryWhiteheadInd looks like this

SELECT TblEvent.TxtEventNo, TblEvent.TextEvent, TblScores.LngCompetitorNo, Sum(TblScores.LngPracticeScore) AS SumOfLngPracticeScore, TblCompetitor.TxtName, TblCompetitor.TxtInitials, TblCompetitor.TxtRank_Rating, TblCompetitor.TxtUnit, TblCompetitor.fWRN, TblCompetitor.TxtClass, TblBisleyYear.BisleyYear
FROM TblBisleyYear, TblCompetitor INNER JOIN (TblEvent INNER JOIN TblScores ON TblEvent.LngEventRef = TblScores.LngEventRef) ON TblCompetitor.LngCompetitorNo = TblScores.LngCompetitorNo
GROUP BY TblEvent.TxtEventNo, TblEvent.TextEvent, TblScores.LngCompetitorNo, TblCompetitor.TxtName, TblCompetitor.TxtInitials, TblCompetitor.TxtRank_Rating, TblCompetitor.TxtUnit, TblCompetitor.fWRN, TblCompetitor.TxtClass, TblBisleyYear.BisleyYear, TblCompetitor.fWhiteheadInd
HAVING (((TblEvent.TextEvent)="Whitehead") AND ((TblCompetitor.fWhiteheadInd)=True))
ORDER BY Sum(TblScores.LngPracticeScore) DESC
WITH OWNERACCESS OPTION;

The important fields are LngCompetitorNo (Person) and SumOfLngPracticeScore (Total of three practice scores)

I hope this helps to sort the problem out.

Best Regards
John

 

[r937]

select A.CompetitorNo
     , A.SumOfLngPracticeScore
     , count(B.SumOfLngPracticeScore)+1 AS Rank
     , 100-count(B.SumOfLngPracticeScore) AS points
  from QryWhiteheadInd AS A 
left outer
  join QryWhiteheadInd AS B 
    on A.[b]SumOfLngPracticeScore[/b]
     < B.[b]SumOfLngPracticeScore[/b]
group 
    by A.CompetitorNo
     , A.SumOfLngPracticeScore
order 
    by A.SumofLngPracticeScore desc

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[bxgti4x4]

Thanks,

I think I should be applying this code to a query based on something but I'm not exactly sure where I should go. Do I add this onto the end of the QryWhiteheadInd or do I have to use that query to make a table?

Sorry if I seem a bit dim, I'm getting slightly out of my depth.

Best Regards
John

 

[bxgti4x4]

r937

Cracked it - Just needed "lng" in front of the CompetitorNo.

I think I can sort it out from here on in.

Thanks very much for all your help

Best Regards
John