MS ACCESS: Calculating rank based on multiple criteria

hello,
perhaps

select t1.id,t1.m, t1.c, t1.nt, count(t2.nt) +1 as cl from T_NOTE as t1 left join T_NOTE as t2 on t1.m=t2.m and t1.c=t2.c and t1.nt<t2.nt group by t1.id,t1.m, t1.c, t1.nt order by t1.c, t1.m, t1.nt desc

Hello yg_be!
Is m=matter, c=class, nt=grade and id=PK of table T_NOTE?

Thanks for the answer. We are awaiting confirmation to try your query.

Hello yg_be

In your query I tried to integrate the student component and I have this:

select t1.Note_id, t1.Eleve_id, t1.Matiere_id, t1.Classe_id, t1.Note, count(t2.Note) +1 as CL
from T_NOTES as t1 left join T_NOTES as t2
on t1.Eleve_id=t2.Eleve_id, t1.Matiere_id=t2.Matiere_id and t1.Classe_id=t2.Classe_id and t1.Note<t2.note group="" by="" t1.note_id="" t1.eleve_id="" t1.matiere_id="" t1.classe_id="" t1.note="" order="" desc="" but="" a="" dialog="" box="" notifies="" me:="" expression="" not="" supported="" to="" be="" clearer="" here="" are="" the="" fields="" of="" t_notes="" table:="" note_id="" eleve_id="" classe_id="" matiere_id="" note="" />

OK

The request, as you proposed it, works. I also notice a field named Expr1000 that references the subject identifiers.
But I’d really like to be able to integrate the student component?

OK. We’ll review it again.

I can't manage it!

The query I tried returns the same rank everywhere
SELECT t1.Note_id, t1.Eleve_id, t1.Matiere_id, t1.Classe_id, t1.Note, Count(t2.Note)+1 AS cl
FROM T_NOTES AS t1
LEFT JOIN T_NOTES AS t2 ON (t1.Classe_id = t2.Classe_id) AND (t1.Matiere_id = t2.Matiere_id) AND (t1.Eleve_id = t2.Eleve_id)
GROUP BY t1.Note_id, t1.Matiere_id, t1.Classe_id, t1.Note, t1.Eleve_id
ORDER BY t1.Classe_id, t1.Matiere_id, t1.Note DESC;
The working query but without the student component
SELECT t1.Note_id, t1.Matiere_id, t1.Classe_id, t1.Note, Count(t2.Note)+1 AS cl
FROM T_NOTES AS t1
LEFT JOIN T_NOTES AS t2 ON (t1.Classe_id = t2.Classe_id) AND (t1.Matiere_id = t2.Matiere_id)
GROUP BY t1.Note_id, t1.Matiere_id, t1.Classe_id, t1.Note
ORDER BY t1.Classe_id, t1.Matiere_id, t1.Note DESC;

Hello!
I mean that the query you proposed on 02/02/2022 at 11:33 works correctly but I can't manage to include the Eleve_id field.

Yes, perfectly.