Solved Grouped ranking

gregoryhills · Feb 14, 2022

Hi. Check REPORT FORMS in REPORTS in this database. I need a query or function to rank Students per class and per the stream at a certain year and a term e.g
Year- 2022
Term- Term 1
Student Name- X
Class position..... Out of ...........(Students in a class)
Stream Position....... Out of(Students in a class)
Lastly how can I refer to last exams total marks per student.
I have highlighted in yellow in the report.
Any help will be highly appreciated.

gregoryhills · Feb 14, 2022

Stream Position......(out of Students in a stream)

gregoryhills · Feb 14, 2022

Someone kindly assist me

Galaxiom · Feb 14, 2022

One way is to do a DCount of students with higher grades where they have the same criteria as the record being ranked then add one.
This function must run for each record so it is very slow.

The number of students can be done with a Count(), GROUP BY the criteria. Then join this to the other query.

Access isn't very good at ranking.

TSQL used in SQL Server has functions ROW_NUMBER and RANK combined with PARTITION BY and ORDER BY that do the job very simply and efficiently.

gregoryhills · Feb 14, 2022

Galaxiom said:
One way is to do a DCount of students with higher grades where they have the same criteria as the record being ranked then add one.
This function must run for each record so it is very slow.

The number of students can be done with a Count(), GROUP BY the criteria. Then join this to the other query.

Access isn't very good at ranking.

TSQL used in SQL Server has functions ROW_NUMBER and RANK combined with PARTITION BY and ORDER BY that do the job very simply and efficiently.

You are complicating even further. I have been working on it for the last two weeks without success. Kindly look at the attached database and assist me. It has given me headaches.

MajP · Feb 14, 2022

You are complicating even further.

FYI,
If you come asking for help and some expert provides a good working answer, you should not criticize and keep on whining. It does not endear people to help you. Try a little sugar.
The dcount idea is a tried and true method in Access for doing a group by query. If not you have to use a subquery. Both methods are here.

Microsoft Access tips:

How to rank records in Microsoft Access

allenbrowne.com

You data is really poor for demonstration. All are in the same stream. It would be helpful to have some data. Grouping by a single stream is not very illustrative.

MajP · Feb 14, 2022

Maybe something like this but the grouping is not clear to me

Code:

SELECT A.stream,
       A.description,
       A.[student name],
       A.years,
       A.gradedesc,
       A.term,
       A.sumofmark,
       (SELECT Count(*) + 1
        FROM   qryallmarks AS b
        WHERE  A.stream = b.stream
               AND A.years = B.years
               AND A.gradedesc = B.gradedesc
               AND B.sumofmark
               AND A.description = b.description
               AND B.sumofmark < A.sumofmark) AS ClassRank
FROM   qryallmarks AS A
ORDER  BY A.description,
          A.years,
          A.gradedesc;

Query1 Query1

Query1 Query1

STREAM	Years	Description	Student Name	GradeDesc	TERM	SumOfMark	ClassRank
WEST	2020	Agriculture	CHARITY CHEPTOO	FORM 4	Term 1	100	1
WEST	2020	Aviation	CHARITY CHEPTOO	FORM 4	Term 1	100	1
WEST	2020	Business Studies	CHARITY CHEPTOO	FORM 4	Term 1	100	1
WEST	2022	Agriculture	SHARON AWINO	FORM 1	Term 1	10	1
WEST	2022	Agriculture	MERCY CHEBET	FORM 1	Term 1	50	2
WEST	2022	Agriculture	SHEILA CHEBET ISAAC	FORM 1	Term 1	50	2
WEST	2022	Agriculture	CLINTON KIPROTICH	FORM 1	Term 1	50	2
WEST	2022	Agriculture	GILBERT KIPKOECH	FORM 1	Term 1	50	2
WEST	2022	Agriculture	GLAVIN KIPKOECH ROP	FORM 1	Term 1	50	2
WEST	2022	Agriculture	MILLICENT CHEPKOECH	FORM 1	Term 1	50	2
WEST	2022	Arabic	MERCY CHEBET	FORM 1	Term 1	30	1
WEST	2022	Arabic	SHARON AWINO	FORM 1	Term 1	30	1
WEST	2022	Arabic	SHEILA CHEBET ISAAC	FORM 1	Term 1	50	3
WEST	2022	Arabic	MILLICENT CHEPKOECH	FORM 1	Term 1	50	3
WEST	2022	Arabic	GLAVIN KIPKOECH ROP	FORM 1	Term 1	50	3
WEST	2022	Art And Design	CLINTON KIPROTICH	FORM 1	Term 1	50	1
WEST	2022	Art And Design	MERCY CHEBET	FORM 1	Term 1	80	2
WEST	2022	Aviation	CLINTON KIPROTICH	FORM 1	Term 1	50	1
WEST	2022	Biology	SHARON AWINO	FORM 1	Term 1	30	1
WEST	2022	Biology	MILLICENT CHEPKOECH	FORM 1	Term 1	50	2
WEST	2022	Biology	CLINTON KIPROTICH	FORM 1	Term 1	50	2
WEST	2022	Electricity	MILLICENT CHEPKOECH	FORM 1	Term 1	50	1
WEST	2022	French	MILLICENT CHEPKOECH	FORM 1	Term 1	50	1

gregoryhills · Feb 14, 2022

Thanks so much. Sum of mark should be grouped.

gregoryhills · Feb 14, 2022

Let me replace the query qryAllMarks with QueryTotalMarks. I appreciate your efforts. Thanks

gregoryhills · Feb 14, 2022

MajP said:
Maybe something like this but the grouping is not clear to me

Code:

SELECT A.stream, A.description, A.[student name], A.years, A.gradedesc, A.term, A.sumofmark, (SELECT Count(*) + 1 FROM qryallmarks AS b WHERE A.stream = b.stream AND A.years = B.years AND A.gradedesc = B.gradedesc AND B.sumofmark AND A.description = b.description AND B.sumofmark < A.sumofmark) AS ClassRank FROM qryallmarks AS A ORDER BY A.description, A.years, A.gradedesc;

Query1 Query1

Query1 Query1

STREAM Years Description Student Name GradeDesc TERM SumOfMark ClassRank
WEST
2020
Agriculture CHARITY CHEPTOO FORM 4 Term 1
100
1
WEST
2020
Aviation CHARITY CHEPTOO FORM 4 Term 1
100
1
WEST
2020
Business Studies CHARITY CHEPTOO FORM 4 Term 1
100
1
WEST
2022
Agriculture SHARON AWINO FORM 1 Term 1
10
1
WEST
2022
Agriculture MERCY CHEBET FORM 1 Term 1
50
2
WEST
2022
Agriculture SHEILA CHEBET ISAAC FORM 1 Term 1
50
2
WEST
2022
Agriculture CLINTON KIPROTICH FORM 1 Term 1
50
2
WEST
2022
Agriculture GILBERT KIPKOECH FORM 1 Term 1
50
2
WEST
2022
Agriculture GLAVIN KIPKOECH ROP FORM 1 Term 1
50
2
WEST
2022
Agriculture MILLICENT CHEPKOECH FORM 1 Term 1
50
2
WEST
2022
Arabic MERCY CHEBET FORM 1 Term 1
30
1
WEST
2022
Arabic SHARON AWINO FORM 1 Term 1
30
1
WEST
2022
Arabic SHEILA CHEBET ISAAC FORM 1 Term 1
50
3
WEST
2022
Arabic MILLICENT CHEPKOECH FORM 1 Term 1
50
3
WEST
2022
Arabic GLAVIN KIPKOECH ROP FORM 1 Term 1
50
3
WEST
2022
Art And Design CLINTON KIPROTICH FORM 1 Term 1
50
1
WEST
2022
Art And Design MERCY CHEBET FORM 1 Term 1
80
2
WEST
2022
Aviation CLINTON KIPROTICH FORM 1 Term 1
50
1
WEST
2022
Biology SHARON AWINO FORM 1 Term 1
30
1
WEST
2022
Biology MILLICENT CHEPKOECH FORM 1 Term 1
50
2
WEST
2022
Biology CLINTON KIPROTICH FORM 1 Term 1
50
2
WEST
2022
Electricity MILLICENT CHEPKOECH FORM 1 Term 1
50
1
WEST
2022
French MILLICENT CHEPKOECH FORM 1 Term 1
50
1

It works. However I am defeated to insert the number of students who did exams. Should be
CLASSRANK: 1 OUT OF (NUMBER OF STUDENTS WHO DID EXAMS.)
E.G MILLICENT CHEPKOECH
CLASSRANK: 1 OUT OF 7 FORM 1 WEST 2022

gregoryhills · Feb 14, 2022

Th

MajP said:
Maybe something like this but the grouping is not clear to me

Code:

SELECT A.stream, A.description, A.[student name], A.years, A.gradedesc, A.term, A.sumofmark, (SELECT Count(*) + 1 FROM qryallmarks AS b WHERE A.stream = b.stream AND A.years = B.years AND A.gradedesc = B.gradedesc AND B.sumofmark AND A.description = b.description AND B.sumofmark < A.sumofmark) AS ClassRank FROM qryallmarks AS A ORDER BY A.description, A.years, A.gradedesc;

Query1 Query1

Query1 Query1

STREAM Years Description Student Name GradeDesc TERM SumOfMark ClassRank
WEST
2020
Agriculture CHARITY CHEPTOO FORM 4 Term 1
100
1
WEST
2020
Aviation CHARITY CHEPTOO FORM 4 Term 1
100
1
WEST
2020
Business Studies CHARITY CHEPTOO FORM 4 Term 1
100
1
WEST
2022
Agriculture SHARON AWINO FORM 1 Term 1
10
1
WEST
2022
Agriculture MERCY CHEBET FORM 1 Term 1
50
2
WEST
2022
Agriculture SHEILA CHEBET ISAAC FORM 1 Term 1
50
2
WEST
2022
Agriculture CLINTON KIPROTICH FORM 1 Term 1
50
2
WEST
2022
Agriculture GILBERT KIPKOECH FORM 1 Term 1
50
2
WEST
2022
Agriculture GLAVIN KIPKOECH ROP FORM 1 Term 1
50
2
WEST
2022
Agriculture MILLICENT CHEPKOECH FORM 1 Term 1
50
2
WEST
2022
Arabic MERCY CHEBET FORM 1 Term 1
30
1
WEST
2022
Arabic SHARON AWINO FORM 1 Term 1
30
1
WEST
2022
Arabic SHEILA CHEBET ISAAC FORM 1 Term 1
50
3
WEST
2022
Arabic MILLICENT CHEPKOECH FORM 1 Term 1
50
3
WEST
2022
Arabic GLAVIN KIPKOECH ROP FORM 1 Term 1
50
3
WEST
2022
Art And Design CLINTON KIPROTICH FORM 1 Term 1
50
1
WEST
2022
Art And Design MERCY CHEBET FORM 1 Term 1
80
2
WEST
2022
Aviation CLINTON KIPROTICH FORM 1 Term 1
50
1
WEST
2022
Biology SHARON AWINO FORM 1 Term 1
30
1
WEST
2022
Biology MILLICENT CHEPKOECH FORM 1 Term 1
50
2
WEST
2022
Biology CLINTON KIPROTICH FORM 1 Term 1
50
2
WEST
2022
Electricity MILLICENT CHEPKOECH FORM 1 Term 1
50
1
WEST
2022
French MILLICENT CHEPKOECH FORM 1 Term 1
50
1

Thanks to you I have found the solution. Thanks so much.

Gasman · Feb 14, 2022

So please share the solution. This is what this site is all about after all?

SHANEMAC51 · Feb 25, 2022

gregoryhills said:
Lastly how can I refer to last exams total marks per student.

I didn't really understand how LASTEXAMS TOTAL MARKS
differs from TOTAL MARKS

SHANEMAC51 · Feb 25, 2022

SHANEMAC51 said:
I didn't really understand how LASTEXAMS TOTAL MARKS
differs from TOTAL MARKS

main query

stream	Student ID	student name	years	gradedesc	term	Sum of mark	Avg- Mark	Class Rank	Stream Rank	Class Rank2 avg	Stream Rank2 avg
WEST	83	MERCY CHEBET	2022	FORM 1	Term 1	260	65	2	2	1	1
WEST	9	SHEILA CHEBET ISAAC	2022	FORM 1	Term 1	180	60	3	4	2	2
WEST	23	GILBERT KIPKOECH	2022	FORM 1	Term 1	120	60	5	6	2	2
WEST	52	CLINTON KIPROTICH	2022	FORM 1	Term 1	280	56	1	1	4	4
WEST	2	MILLICENT CHEPKOECH	2022	FORM 4	Term 1	250	50	1	3	1	5
WEST	15	GLAVIN KIPKOECH ROP	2022	FORM 1	Term 1	100	50	6	7	5	5
WEST	65	SHARON AWINO	2022	FORM 1	Term 1	160	40	4	5	6	7

Solved Grouped ranking

gregoryhills

New member

Attachments

gregoryhills

New member

gregoryhills

New member

Galaxiom

Super Moderator

gregoryhills

New member

MajP

You've got your good things, and you've got mine.

Microsoft Access tips:

MajP

You've got your good things, and you've got mine.

gregoryhills

New member

gregoryhills

New member

gregoryhills

New member

gregoryhills

New member

Gasman

Enthusiastic Amateur

SHANEMAC51

Active member

Attachments

SHANEMAC51

Active member

Similar threads

Users who are viewing this thread