Sql Code Ranking

[Jingels]

Hello,

with my SQL code i can make a ranking. But if there are two or more with the same time and score the ranking is wrong.
lets say if there are two with the same score and time the code gives them 4 place instead of place 3.

SELECT a.RallyKlasnaam, a.naamhond, a.Exhibitor, a.Rallydognr, a.Score, a.Time, Round(CDbl(a.[Score]+(1-a.[Time])),5) AS Points, Count(*) AS Rank
FROM Tbl_ResultsRally AS a INNER JOIN Tbl_ResultsRally AS b ON (Round(CDbl(a.[Score]+(1-a.[Time])),5)<=Round(CDbl(b.[Score]+(1-b.[Time])),5)) AND (a.RallyKlasnaam = b.RallyKlasnaam)
GROUP BY a.RallyKlasnaam, a.naamhond, a.Exhibitor, a.Rallydognr, a.Score, a.Time
ORDER BY a.RallyKlasnaam, a.Score;

thanks a lot.

 

[arnelgp]

fist create a query, named it qryForRanking.

SELECT RallyKlasnaam, naamhond, Exhibitor, Rallydognr, Score, Time
FROM Tbl_ResultsRally
ORDER BY RallyKlasnaam, naamhond, Exhibitor, Rallydognr, Score, Time;

Now paste the function below in a Module, that you will use for ranking:

public function fnRanking(pRallyKlasnaam, pnaamhond, pExhibitor, pRallydognr, pQuery as string) as long
dim s as string
dim l as long
dim d as dao.database
dim r as dao.recordset

set d = currentdb
set r = d.querydefs(pQuery).Openrecordset(dbopendynaset)
with r
    if not (.bof and .eof) then 
        .movefirst
        .findfirst "[RallyKlassnaam] = '" & pRallyKlassnaam & "' And " & _
            "[naamhond] = '" & pnaamhond & "' And " & _
            "[Exhibitor] = '" & pExhibitor & "' And " & _
            "[Rallydognr] = '" & pRallydognr & "'"
        while not .bof
            if s <> ![score] & ![time] then
                l = l + 1
                s = ![score] & ![time]
            end if
            .moveprevious
        wend
    end if
    .close
end with
fnRaking = l
end function

create a new query that will show this ranking:


SELECT RallyKlasnaam, naamhond, Exhibitor, Rallydognr, Score, Time, fnRanking([RallyKlassnaam], [naamhond], [Exhibitor], [Rallydognr], "qryForRanking") As Ranking
FROM Tbl_ResultsRally
ORDER BY RallyKlasnaam, naamhond, Exhibitor, Rallydognr, Score, Time;

 

[Jingels]

Hello,
thanks alot,but i get a fault, The expression contains an undefined function fnRanking.
what am I doing wrong.
i have made de first query, the code i put it a module, And make the second code called test for now.

 

[Jingels]

Now i get a error in [Ranking].test
So this is my code in "qryForRanking"

SELECT Tbl_ResultsRally.RallyKlasnaam, Tbl_ResultsRally.naamhond, Tbl_ResultsRally.Exhibitor, Tbl_ResultsRally.Rallydognr, Tbl_ResultsRally.Score, Tbl_ResultsRally.Time
FROM Tbl_ResultsRally;

this is the public function

Public Function fnRank(RallyKlasnaam, naamhond, Exhibitor, Rallydognr, test As Long) As Long
Dim s As String
Dim l As Long
Dim d As dao.Database
Dim r As dao.Recordset
 Set d = CurrentDb
Set r = d.QueryDefs(test).OpenRecordset(dbOpenDynaset)
With r
 If Not (.BOF And .EOF) Then
 .MoveFirst
 .FindFirst "[RallyKlasnaam] = '" & RallyKlasnaam & "' And " & _
 "[naamhond] = '" & naamhond & "' And " & _
 "[Exhibitor] = '" & Exhibitor & "' And " & _
 "[Rallydognr] = '" & Rallydognr & "'"
 While Not .BOF
 If s <> ![Score] & ![Time] Then
 l = l + 1
 s = ![Score] & ![Time]
 End If
 .MovePrevious
 Wend
 End If
 .Close
End With
fnRank = l
End Function

And this is the second query, "test"

SELECT Tbl_ResultsRally.RallyKlasnaam, Tbl_ResultsRally.naamhond, Tbl_ResultsRally.Exhibitor, Tbl_ResultsRally.Rallydognr, Tbl_ResultsRally.Score, Tbl_ResultsRally.Time, fnRank([RallyKlasnaam],[naamhond],[Exhibitor],[Rallydognr],"qryForRanking") AS Ranking
FROM Tbl_ResultsRally;

what is going wrong here. I must tell that the Time is a number and no Date/time because of the millisecond that i have to enter.

 

[Grumm]

Why did you changed all the parameters of that function ?
And why did you change the string into a long ?
Is your query "qryForRanking" a number ? Maybe that is your main problem why it doesn't work.

 

[Jingels]

oke sorry i thought i had to fit it to my DB. i have put every ting back and now i get; undedined Function 'fnRanking' in expression !!!

[Time] and [Score] "Tbl_ResultsRally" is are numbers

 

[Jingels]

undedined Function 'fnRanking' in expression this is gone now. My stipid misstake i had call de module the same as the fuction.

But now is there a Ranking #Error !!!!!

 

[arnelgp]

what are the fieldtypes on your table of the following:

RallyKlasnaam
naamhond
Exhibitor
Rallydognr

 

[Jingels]

RallyKlasnaam- text
naamhond-text
Exhibitor-text
Rallydogn- number

 

[arnelgp]

Public Function fnRank(RallyKlasnaam, naamhond, Exhibitor, Rallydognr, test As string) As Long
Dim s As String
Dim l As Long
Dim d As dao.Database
Dim r As dao.Recordset
Set d = CurrentDb
Set r = d.QueryDefs(test).OpenRecordset(dbOpenDynaset)
With r
If Not (.BOF And .EOF) Then
.MoveFirst
.FindFirst "[RallyKlasnaam] = '" & RallyKlasnaam & "' And " & _
"[naamhond] = '" & naamhond & "' And " & _
"[Exhibitor] = '" & Exhibitor & "' And " & _
"[Rallydognr] = " & Rallydognr
While Not .BOF
If s <> ![Score] & ![Time] Then
l = l + 1
s = ![Score] & ![Time]
End If
.MovePrevious
Wend
End If
.Close
End With
fnRank = l
End Function

edit:

your qryForRanking should be sorted in this manner:

SELECT RallyKlasnaam, naamhond, Exhibitor, Rallydognr, Score, Time
FROM Tbl_ResultsRally
ORDER BY Score DESC, Time, RallyKlasnaam, naamhond, Exhibitor, Rallydognr;

same with the new query you made:

SELECT RallyKlasnaam, naamhond, Exhibitor, Rallydognr, Score, Time, fnRanking([RallyKlassnaam], [naamhond], [Exhibitor], [Rallydognr], "qryForRanking") As Ranking
FROM Tbl_ResultsRally
ORDER BY Score DESC, Time, RallyKlasnaam, naamhond, Exhibitor, Rallydognr ;

 

[Jingels]

Thanks it works.