How to Create a Ranking System within a record

[channiekwon]

Hello,

I'm creating an exam results summary database and I'm kinda stuck.

I have 2 tests:

First is the personality test that measures the degree of motivation, extroversion, and leadership.
I input the scores and I get Access to return the values in percentage form so I can rank the three qualities in descending order
to know if the person has a higher degree of motivation or extroversion or leadership qualities

It's also the same with my second test in which I input the number of strengths and weaknesses of a person which corresponds to a certain temperament
and I get access to return the total for each temperament so I can also rank what is the dominant temperament of that person

I don't want to rank which person has the highest degree of motivation or extraversion bla bla
or which person has the highest number of strengths/weaknesses bla bla
I just want to rank within the person's record only and not in comparison with other persons
(i hope what I'm saying is understandable )

Please refer to the image below so you can see what I'm trying to say:

For example, Person A has these test results


So from the image above:
I want Access to display that that person has a higher degree of motivation, next is his leadership, and the least is his extroversion
And for his temperament, i want Access to display that his two dominant types are first Choleric, and then next is Melancholic/Phlegmatic, and last is Sanguine.

I started by identifying the highest percentage and I used the code (and in the same format, i also did it with the second test)



=IIf([PT_MotivationDegree]>[PT_ExtraversionDegree] And [PT_MotivationDegree]>[PT_LeadershipDegree],"Motivation",IIf([PT_ExtraversionDegree]>[PT_MotivationDegree] And [PT_ExtraversionDegree]>[PT_LeadershipDegree],"Extraversion",IIf([PT_LeadershipDegree]>[PT_MotivationDegree] And [PT_LeadershipDegree]>[PT_ExtraversionDegree],"Leadership","")))

But this wouldn't work if there were 2 fields with the same value, right? (Like in my problem above)
And the codes are really long too

I don't quite understand queries so please forgive me~

[ranman256]

I have a table with the ITEM and [rankA], [rankB], [rankC]
yours would be

Person, motivation, extroversion, and leadership, [rankM], [rankX], [rankL]
the code below marks the RANK field after sorting on the field (descending), then marking the rank.,

usage:
RANK "qsSortMotovate", "RankM"
RANK "qsSortXtro", "RankX"
RANK "qsSortLeader", "RankL"

Public Sub Rank(pvQry, ByVal pvRankFld)
Dim rst   'As Recordset
Dim lNum As Long


lNum = 1
Set rst = CurrentDb.OpenRecordset(pvQry)
With rst
    While Not .EOF
         .Fields(pvRankFld) = lNum
         .Update
         lNum = lNum + 1
         
         .MoveNext
    Wend
End With
Set rst = Nothing
End Sub