Ranking scores within a subset of records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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])

===============================
 
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;
 
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
 
thanks much. Syntax just kills me sometimes. I had been looking at that
line for hours.

Steve

Marshall Barton said:
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
Click to expand...
 
Guest said:
thanks much. Syntax just kills me sometimes. I had been looking at that
line for hours.

Steve

"Marshall Barton" wrote:

> Steve S wrote:
>
> > 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
>
>
> You need the entryID outside the quotes as you did with
> score.
>
> . . . & " And [Entry ID]=" & [Entry ID]) AS Rank
> --
> Marsh
> MVP [MS Access]
>
Click to expand...
I know this is a way dead thread, but taking a chance. This is almost what I need. However, I need each judge to rank the competitor. What I need is ranked below:

Twirler EntryID Score Judge Rank
Savannah Miller 8499 96.00 A 1
Ashley Meyer 9236 86.70 A 2
Jacy Alexander 9526 85.00 A 3
Haley Schlattmann 9039 78.50 A 4
Savannah Miller 8499 89.00 B 1
Jacy Alexander 9526 87.50 B 2
Haley Schlattmann 9039 86.50 B 3
Ashley Meyer 9236 80.90 B 4
Ashley Meyer 9236 85.90 C 1
Savannah Miller 8499 83.00 C 2
Haley Schlattmann 9039 82.70 C 3
Jacy Alexander 9526 80.50 C 4
 
Back
Top