1st, 2nd 3rd scores How to?

[Rhonda B]

I have created a report which shows the final scores for indiviual
contestants and want the report to list the placings in order.
at present i have a text box which shows a number associated to the
decending percentage eg.
1 contestants name 86%
2 '' '' '' '' 76%
3 " " " " 76%
4 " " " " 68%
but as you can see it wont give the two exact same percentages an equal
score.

the contestants with 76% should both be 2. Like This
1 contestants name 86%
2 '' '' '' '' 76%
2 " " " " 76%
3 " " " " 68%
the text box properties I have # as the format and control sourse is "=1"
with running sum "over group"
What can i change to get it giving the correct placings?

 

[Tom Ellison]

Dear Rhonda:

Actually, perhaps the rankings should be:

1 contestants name 86%
2 '' '' '' '' 76%
2 " " " " 76%
4 " " " " 68%

Fourth place is the one with 3 higher ranked scores.

In any case, either is possible. I can give you examples if you give me the
SQL to a query that reports the <contentant name> and the <score>, something
like:

SELECT Contestant, Score
FROM TableName

The queries to do this are:

SELECT Contestant, Score,
(SELECT COUNT(*) FROM TableName T2
WHERE T2.Score > T1.Score) + 1
AS Rank
FROM TableName

the other result can be done with 2 queries, the first I'll name
DistinctScore

SELECT DISTINCT Score
FROM TableName

Call this one Q1

SELECT Contestant, Score,
(SELECT COUNT(*) FROM Q1
WHERE Q1.Score > T1.Score) + 1
AS RANK
FROM TableName

If you want this sorted, write another query that sorts, like:

SELECT Contestant, Score, Rank
FROM QueryName
ORDER BY Rank DESC

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison
Microsoft Access MVP

 

[Tom Wickerath]

Hi Tom,

I tried your examples out, but I needed to add "AS T1" to the end of each
SQL statement, in order to get it to work. For example, the first SQL
statement was:

SELECT Contestant, Score,
(SELECT COUNT(*) FROM TableName T2
WHERE T2.Score > T1.Score) + 1
AS Rank
FROM TableName

I had to add the part indicated by the arrow:

SELECT Contestant, Score,
(SELECT COUNT(*) FROM TableName T2
WHERE T2.Score > T1.Score) + 1
AS Rank
FROM TableName AS T1 <-------------------------


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Ellison]

Dear Tom:

I expect you are almost certainly testing this using the Jet database. Fair
enough. Probably that is the requirement of the original poster.

Did you notice that it required an AS in the place where you put it, but did
not require AS for the alias of T2. Such inconsistent rules, which also
exceed the requirements of the SQL specification, are going to continue to
be a problem for me. I guess I need someone like you checking up on me.
So, thanks!

For future postings, I'll try to use AS in every place where I have
understood it to be optional.

Tom Ellison
Microsoft Access MVP

 

[Tom Wickerath]

Hi Tom,

I expect you are almost certainly testing this using the Jet database.

Yep. Given that probably > 95% of the people posting to this newsgroup are
using JET, that would not be an unreasonable assumption.

I did not notice the inconsitencies, nor was I aware of them.

And, I was not checking up on you. Rather, I'm interested in learning more
about subqueries myself. The ranking query is pretty cool. So, I created a
table, populated it with some data, and tried your suggested SQL statement. I
couldn't get it to work at first. However, the error message was pretty easy
to figure out. That's why I posted back--to help save the OP from some
frustration if this person tried out your suggestion.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Ellison]

Dear Tom:

It's always appropriate to look after the best interest of the original
poster, and others looking on in this forum. About the basic facts here,
you are entirely correct. Given that this must work in Jet, it needs to be
made certain that it will work in Jet, no matter how perverse the vagueries
involved. I can only thank you for your assistance.

It is not, however, inappropriate to be pointing out these issues for the
common information of all who read.

So, again, thanks!

Tom Ellison
Microsoft Access MVP

 

[Tom Wickerath]

Okay, I just didn't want you to have the wrong impression that I was checking
your work ("I guess I need someone like you checking up on me". My interests
were solely in improving my own skills, by trying out your solution.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Ellison]

Dear Tom:

Hey, the purpose of these newsgroups is served by what you have done. I
appreciate it!

Tom Ellison
Microsoft Access MVP