Number Crunching

[Space Cowboy]

Good Morning Good People,

when I am ranking items frequently the output is not as required,

ie

10	1
9	3
9	3
8	4
7	5

I would like the rank to be interpreted as follows

10	1
10	1
9	2
8	3
8	3
7	4
7	4

Is there a global setting that can take care of this or is there some other procedure that I must use.

All advice gratefully received.

 

[arnelgp]

you can use a Query for that:

SELECT [NumberField], (SELECT Count("*") From [yourTableName] As T1 Where T1.[NumberField] > [yourTableName].[NumberField]) + 1 As Rank From [yourTableName] Order By [NumberField] DESC;

 

[arnelgp]

sorry, my query won't work.
try using a function, put this in a Module:

'arnelgp
Public Function fnSpecialRank(ByVal lngValue As Long) As Long
' replace with your correct table and numeric fieldname
Const TABLE_NAME As String = "yourTableName"
Const FIELD_NAME As String = "NumberField"

    Dim db As DAO.Database
    Dim n As Long
    Dim dict As Object
    Set db = CurrentDb
    Set dict = CreateObject("scripting.dictionary")
    With db.OpenRecordset("select [" & FIELD_NAME & "] From [" & TABLE_NAME & "] Order By [" & FIELD_NAME & "] DESC;", dbOpenSnapshot, dbReadOnly)
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            If dict.Exists(.Fields(FIELD_NAME) & "") = False Then
                dict(.Fields(FIELD_NAME) & "") = 1
            End If
            If .Fields(FIELD_NAME) = lngValue Then
                Exit Do
            End If
            .MoveNext
        Loop
        .Close
    End With
    fnSpecialRank = dict.Count
End Function

and on your query:

SELECT yourTableName.[NumberField], fnSpecialRank([NumberField]) AS Rank
FROM yourTableName
ORDER BY yourTableName.[NumberField] DESC;

 

[Space Cowboy]

thanks @arnelgp

the rank is generated in a query.
should I let it run, and then a new query to convert
or try to modify in the query?

 

[arnelgp]

i will provide you a working demo.
see Module1 (in VBA) for the rank function, close it.
the run qryRankDemo.

 

[ebs17]

The rank generation should then use a different source.

' not
SELECT item FROM TabX
' rather
SELECT DISTINCT item FROM TabX

In a second step, the determined rank would then have to be assigned to all records in the TabX table by linking them via item. This can all be solved using a query. Perhaps you'll dare to try it yourself after reading the description.

 

[Space Cowboy]

I will try suggestions

Thank you for advice

The penny has dropped with the "select Distinct"

Eberhard you are always right.