By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,524 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Ranking problem in MS ACCESS

P: 2
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 :
Expand|Select|Wrap|Line Numbers
  1. ID    NAME    SCHOOL    CLASS    SEC    MARKS
  2. 001    aaa    xxx    V    A    90
  3. 002    bbb    xxx    V    B    80
  4. 003    ccc    xxx    V    A    70
  5. 004    ddd    xxx    V    A    85
  6. 005    eee    xxx    V    B    82
  7. 006    fff    xxx    V    A    75
  8. 007    ggg    xxx    VI    A    92
  9. 008    hhh    xxx    VI    B    83
  10. 009    iii    xxx    VI    A    74
  11. 010    jjj    xxx    VI    A    86
  12. 011    kkk    xxx    VI    B    81
  13. 012    lll    xxx    VI    A    76
  14. 013    mmm    yyy    V    A    91
  15. 014    nnn    yyy    V    B    81
  16. 015    ooo    yyy    V    A    71
  17. 016    ppp    yyy    V    A    86
  18. 017    qqq    yyy    V    B    83
  19. 018    rrr    yyy    V    A    76
  20. 019    sss    yyy    VI    A    93
  21. 020    ttt    yyy    VI    B    84
  22. 021    uuu    yyy    VI    A    75
  23. 022    vvv    yyy    VI    A    87
  24. 023    www    yyy    VI    B    82
  25. 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 :
Expand|Select|Wrap|Line Numbers
  1. ID    NAME    SCHOOL    CLASS    SEC    MARKS    RANK1    RANK2    RANK3    
  2. 001    aaa    xxx    V    A    90    1    1    2
  3. 002    bbb    xxx    V    B    80    2    4    8
  4. 003    ccc    xxx    V    A    70    4    6    12
  5. 004    ddd    xxx    V    A    85    2    2    4
  6. 005    eee    xxx    V    B    82    1    3    6
  7. 006    fff    xxx    V    A    75     3     5    10
  8. 007    ggg    xxx    VI    A    92    1    1    2
  9. 008    hhh    xxx    VI    B    83    2    3    8
  10. 009    iii    xxx    VI    A    74    4    6    12
  11. 010    jjj    xxx    VI    A    86    2    2    4    
  12. 011    kkk    xxx    VI    B    81    1    4    6
  13. 012    lll    xxx    VI    A    76    3    5    10
  14. 013    mmm    yyy    V    A    91    1    1    1
  15. 014    nnn    yyy    V    B    81    2    4    7
  16. 015    ooo    yyy    V    A    71    4    6    11
  17. 016    ppp    yyy    V    A    86    2    2    3
  18. 017    qqq    yyy    V    B    83    1    3    5
  19. 018    rrr    yyy    V    A    76    3    5    9
  20. 019    sss    yyy    VI    A    93    1    1    1
  21. 020    ttt    yyy    VI    B    84    2    4    7
  22. 021    uuu    yyy    VI    A    75    4    6    11
  23. 022    vvv    yyy    VI    A    87    2    2    3
  24. 023    www    yyy    VI    B    82    1    3    5
  25. 024    xxx    yyy    VI    A    77    3    5    9
Please help me sir.


Thanking You

Regards,

Indra Nath Lahiry
Mail : <Removed>
Mar 12 '12 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,186
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).
Mar 12 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.