Ranking problem in MS ACCESS

[Indra Lahiry]

Dear Sir,
Would you like to help me please. I am creating a database with some queries in MS ACCESS and facing a problem regarding RANKING. My database based on student performance, where a table contains student ID, NAME, SCHOOL, CLASS, SEC, MARKS. My table looks like :

ID	NAME	SCHOOL	CLASS	SEC	MARKS
001	aaa	xxx	V	A	90
002	bbb	xxx	V	B	80
003	ccc	xxx	V	A	70
004	ddd	xxx	V	A	85
005	eee	xxx	V	B	82
006	fff	xxx	V	A	75
007	ggg	xxx	VI	A	92
008	hhh	xxx	VI	B	83
009	iii	xxx	VI	A	74
010	jjj	xxx	VI	A	86
011	kkk	xxx	VI	B	81
012	lll	xxx	VI	A	76
013	mmm	yyy	V	A	91
014	nnn	yyy	V	B	81
015	ooo	yyy	V	A	71
016	ppp	yyy	V	A	86
017	qqq	yyy	V	B	83
018	rrr	yyy	V	A	76
019	sss	yyy	VI	A	93
020	ttt	yyy	VI	B	84
021	uuu	yyy	VI	A	75
022	vvv	yyy	VI	A	87
023	www	yyy	VI	B	82
024	xxx	yyy	VI	A	77

Now I need a query where I can RANK the student by following criteria :
1. Rank on the basis of own SEC. i.e. RANK1
2. Rank on the basis of own CLASS within the school. i.e. RANK2
3. Rank on the basis of CLASS from all school. i.e. RANK3
And the Query look like :

ID	NAME	SCHOOL	CLASS	SEC	MARKS	RANK1	RANK2	RANK3	
001	aaa	xxx	V	A	90	1	1	2
002	bbb	xxx	V	B	80	2	4	8
003	ccc	xxx	V	A	70	4	6	12
004	ddd	xxx	V	A	85	2	2	4
005	eee	xxx	V	B	82	1	3	6
006	fff	xxx	V	A	75 	3 	5	10
007	ggg	xxx	VI	A	92	1	1	2
008	hhh	xxx	VI	B	83	2	3	8
009	iii	xxx	VI	A	74	4	6	12
010	jjj	xxx	VI	A	86	2	2	4	
011	kkk	xxx	VI	B	81	1	4	6
012	lll	xxx	VI	A	76	3	5	10
013	mmm	yyy	V	A	91	1	1	1
014	nnn	yyy	V	B	81	2	4	7
015	ooo	yyy	V	A	71	4	6	11
016	ppp	yyy	V	A	86	2	2	3
017	qqq	yyy	V	B	83	1	3	5
018	rrr	yyy	V	A	76	3	5	9
019	sss	yyy	VI	A	93	1	1	1
020	ttt	yyy	VI	B	84	2	4	7
021	uuu	yyy	VI	A	75	4	6	11
022	vvv	yyy	VI	A	87	2	2	3
023	www	yyy	VI	B	82	1	3	5
024	xxx	yyy	VI	A	77	3	5	9

Please help me sir.


Thanking You

Regards,

Indra Nath Lahiry
Mail : <Removed>

[NeoPa]

Ranking is determined by simply counting the records that match the group specified to rank within and are greater than the current value (Then adding one of course).