Ranking scores within a subset of records

S

Steve S

I need to rank the scores each judge gives a competitor in an event. I will
be using a query based on 4 or 5 tables but have created a table with the
fields Ineed to make the SQL easier to follow.

what I have is
Twirler Entry ID Score Judge Rank
Savannah Miller 8499 96.00 A
Savannah Miller 8499 89.00 B
Savannah Miller 8499 82.00 C
Haley Schlattmann 9039 86.50 B
Haley Schlattmann 9039 78.50 A
Ashley Meyer 9236 86.70 A
Ashley Meyer 9236 85.90 C
Ashley Meyer 9236 80.90 B
Jacy Alexander 9526 87.50 B
Jacy Alexander 9526 85.00 A

This is the SQL I have started with. I know I need some sort of "Where" to
keep the function operating within a set range so I added the "entry Id
=entry Id " but that does not work. It ranks all 10 scores (correctly).

SELECT [Test Rank].Twirler, [Test Rank].[Entry ID], [Test Rank].Score, [Test
Rank].Judge, 1+DCount("*","Test Rank","[score]>" & [score] & " And [Entry
ID]=[Entry ID] ") AS Rank
FROM [Test Rank];

What I need is the Rank as shown below

Twirler Entry ID Score Judge Rank
Savannah Miller 8499 96.00 A 1
Savannah Miller 8499 89.00 B 2
Savannah Miller 8499 82.00 C 3
Haley Schlattmann 9039 86.50 B 1
Haley Schlattmann 9039 78.50 A 2
Ashley Meyer 9236 86.70 A 1
Ashley Meyer 9236 85.90 C 2
Ashley Meyer 9236 80.90 B 3
Jacy Alexander 9526 87.50 B 1
Jacy Alexander 9526 85.00 A 2

Any help is appreciated.

Steve
 
O

Ofer Cohen

Not sure why you need it, but take it under account that the resault will be
different every time there is more record inserted
Also the record must be sorted the same
================================
Create two global variable within a module
Option Compare Database

Global OldValue As Double
Global MyOldName As String
================================
Create a function in a module to return the counter

Function GetNextNum(MyName As String) As Long
If Nz(MyOldName, "") <> MyName Then
OldValue = 1
MyOldName = MyName
Else
OldValue = OldValue + 1
End If
GetNextNum = OldValue
End Function
===============================
In the query add a field

Rank : GetNextNum([Twirler])

===============================
 
M

Michael Gramelspacher

I need to rank the scores each judge gives a competitor in an event. I will
be using a query based on 4 or 5 tables but have created a table with the
fields Ineed to make the SQL easier to follow.

what I have is
Twirler Entry ID Score Judge Rank
Savannah Miller 8499 96.00 A
Savannah Miller 8499 89.00 B
Savannah Miller 8499 82.00 C
Haley Schlattmann 9039 86.50 B
Haley Schlattmann 9039 78.50 A
Ashley Meyer 9236 86.70 A
Ashley Meyer 9236 85.90 C
Ashley Meyer 9236 80.90 B
Jacy Alexander 9526 87.50 B
Jacy Alexander 9526 85.00 A

This is the SQL I have started with. I know I need some sort of "Where" to
keep the function operating within a set range so I added the "entry Id
=entry Id " but that does not work. It ranks all 10 scores (correctly).

SELECT [Test Rank].Twirler, [Test Rank].[Entry ID], [Test Rank].Score, [Test
Rank].Judge, 1+DCount("*","Test Rank","[score]>" & [score] & " And [Entry
ID]=[Entry ID] ") AS Rank
FROM [Test Rank];

What I need is the Rank as shown below

Twirler Entry ID Score Judge Rank
Savannah Miller 8499 96.00 A 1
Savannah Miller 8499 89.00 B 2
Savannah Miller 8499 82.00 C 3
Haley Schlattmann 9039 86.50 B 1
Haley Schlattmann 9039 78.50 A 2
Ashley Meyer 9236 86.70 A 1
Ashley Meyer 9236 85.90 C 2
Ashley Meyer 9236 80.90 B 3
Jacy Alexander 9526 87.50 B 1
Jacy Alexander 9526 85.00 A 2

Any help is appreciated.

Steve
Click to expand...
Try this:

SELECT b.Twirler,
b.EntryID,
b.Score,
b.Judge,
(SELECT COUNT(* ) AS Rank
FROM TwirlingCompetition AS a
WHERE a.Twirler = b.Twirler
AND a.EntryID = b.EntryID
AND a.Score >= b.Score) AS Rank
FROM TwirlingCompetition AS b
GROUP BY b.Twirler,b.EntryID,b.Score,b.Judge
ORDER BY b.EntryID,
b.Score DESC;
 
M

Marshall Barton

Steve said:
I need to rank the scores each judge gives a competitor in an event. I will
be using a query based on 4 or 5 tables but have created a table with the
fields Ineed to make the SQL easier to follow.

what I have is
Twirler Entry ID Score Judge Rank
Savannah Miller 8499 96.00 A
Savannah Miller 8499 89.00 B
Savannah Miller 8499 82.00 C
Haley Schlattmann 9039 86.50 B
Haley Schlattmann 9039 78.50 A
Ashley Meyer 9236 86.70 A
Ashley Meyer 9236 85.90 C
Ashley Meyer 9236 80.90 B
Jacy Alexander 9526 87.50 B
Jacy Alexander 9526 85.00 A

This is the SQL I have started with. I know I need some sort of "Where" to
keep the function operating within a set range so I added the "entry Id
=entry Id " but that does not work. It ranks all 10 scores (correctly).

SELECT [Test Rank].Twirler, [Test Rank].[Entry ID], [Test Rank].Score, [Test
Rank].Judge, 1+DCount("*","Test Rank","[score]>" & [score] & " And [Entry
ID]=[Entry ID] ") AS Rank
FROM [Test Rank];

What I need is the Rank as shown below

Twirler Entry ID Score Judge Rank
Savannah Miller 8499 96.00 A 1
Savannah Miller 8499 89.00 B 2
Savannah Miller 8499 82.00 C 3
Haley Schlattmann 9039 86.50 B 1
Haley Schlattmann 9039 78.50 A 2
Ashley Meyer 9236 86.70 A 1
Ashley Meyer 9236 85.90 C 2
Ashley Meyer 9236 80.90 B 3
Jacy Alexander 9526 87.50 B 1
Jacy Alexander 9526 85.00 A 2
Click to expand...


You need the entryID outside the quotes as you did with
score.

. . . & " And [Entry ID]=" & [Entry ID]) AS Rank
 
Top