Number Crunching (1 Viewer)

Space Cowboy

Member
Local time
Today, 18:59
Joined
May 19, 2024
Messages
245
Good Morning Good People,

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

ie

101
93
93
84
75

I would like the rank to be interpreted as follows

101
101
92
83
83
74
74

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.
 
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;
 
sorry, my query won't work.
try using a function, put this in a Module:

Code:
'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:

Code:
SELECT yourTableName.[NumberField], fnSpecialRank([NumberField]) AS Rank
FROM yourTableName
ORDER BY yourTableName.[NumberField] DESC;
 
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?
 
i will provide you a working demo.
see Module1 (in VBA) for the rank function, close it.
the run qryRankDemo.
 

Attachments

The rank generation should then use a different source.
Code:
' 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.
 
I will try suggestions

Thank you for advice (y)

The penny has dropped with the "select Distinct" (y)

Eberhard you are always right.
 

Users who are viewing this thread

Back
Top Bottom