Need to break tie in ranking using date

[swestoyz]

Hello there,

I am fairly new to custom SQL statements in Access, so I'll do my best to communicate my request.

I am being tasked with ranking people based on a set score. The tie breaker would be their hire date, oldest date would receive the better rank. Higher the score, better the rank. with the code I found on a Microsoft support page, ID's 13 and 14 would be ranked 2 as a tie, instead I want the oldest date to tie break:

ID Score EAD Rank
12 96 1/1/1999 1
13 78 1/2/2001 2
14 78 3/1/2002 3
15 65 4/1/1998 4

(Select Count(*) from q_Ranking_Formu la Where [Score] > [Rank1].[Score]+1) AS Rank
FROM q_Ranking_Formu la AS Rank1
ORDER BY Rank1.Score DESC , Rank1.EAD;

Any suggestions? A huge thanks in advance.

Ben

[Rabbit]

Put in an additional condition in your where clause that also compares the dates for when the scores are the same. Also, I'm pretty sure that +1 is supposed to be outside of the subquery.

[ADezii]

Not sure as far as the SQL goes, but this is a relatively simple matter to resolve using Recordset processing. Simply add a Field named [Rank]{LONG}, then execute the following Code. It always allows for a 3rd Tie Breaker (earliest [ID]) should [Score] and [EAD] be equal.

Dim MyDB As DAO.Database
Dim rstRanking As DAO.Recordset
Dim strSQL As String
Dim lngRank As Integer

strSQL = "SELECT [Score],[EAD],[ID],[Rank] FROM q_Ranking_Formula " & _
         "ORDER BY [Score] DESC,[EAD] ASC, [ID] ASC;"

Set MyDB = CurrentDb
Set rstRanking = MyDB.OpenRecordset(strSQL, dbOpenDynaset)

With rstRanking
  Do While Not .EOF
    lngRank = lngRank + 1
      .Edit
        ![Rank] = lngRank
      .Update
        .MoveNext
  Loop
End With

rstRanking.Close
Set rstRanking = Nothing

Sample Data:

ID   Score	EAD	    Rank
1	96	10/12/2002	 4
2	96	1/1/1996	   2
3	96	1/19/1999	  3
3	97	6/8/2009	   1
4	78	1/2/2001	   6
5	78	3/1/2002	  10
6	65	4/1/1998	  11
7	78	3/30/2001	  8
8	78	3/30/2001	  9
9	78	3/17/2001	  7
10   79	8/17/2006	  5

Results ([Rank] Ascending):

ID   Score	EAD	    Rank
3	97	6/8/2009	   1
2	96	1/1/1996	   2
3	96	1/19/1999	  3
1	96	10/12/2002	 4
10   79	8/17/2006	  5
4	78	1/2/2001	   6
9	78	3/17/2001	  7
7	78	3/30/2001	  8
8	78	3/30/2001	  9
5	78	3/1/2002	  10
6	65	4/1/1998	  11

[swestoyz]

Thanks for the replies guys. I was hoping to attempt this without code, so I'll keep ADezii's suggestion in my back pocket.

Thanks for the tip on the +1 Rabbit!

I know why this doesn't work, however, I'm not sure how to add a condition to the Where clause to check for a duplicate rank before checking the date:

Rank: (Select Count(*) from q_Ranking_Formula Where [Score] > [Rank1].[Score] and [EAD] > [Rank1].[EAD])+1

Any suggestions?

[Rabbit]

It should be

Where Score > Rank1.Score Or
(Score = Rank1.Score And EAD > Rank1.EAD)

[swestoyz]

Holy crow, it worked! The only modification I made was to sort the rank for when the tie was issued by changing the greater than to a less than between the EAD and Rank1.EAD.

Thanks again Rabbit!

[Rabbit]

No problem, good luck.