none
Problem in RANKING in MS ACCESS RRS feed

  • Question

  •  

    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                    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                 5

    018          rrr              yyy              V            A            76              3           5        9

    019          sss             yyy             VI            A            93              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               5

    024           xxx             yyy             VI            A            77              3         5        9

    Please help me sir.

     

     

    Thanking You

     

    Regards,

     

    Indranath Lahiry

    Mail : indranathlahiry@yahoo.com




    Friday, January 6, 2012 6:53 AM

Answers

  • see the link above that i gave you.  I think you'll be looking for something on quering with Microsoft Access.  There are also books on this topic, and books which have chapters on this topic.
    --rms www.rmschneider.com
    Friday, January 6, 2012 7:29 AM

All replies

  • This is an online forum provided by Microsoft about their product "Microsoft Project".  You'll have better luck with your question if you re-post in a more appropriate forum. See http://www.microsoft.com/communities/forums/default.mspx.
    --rms www.rmschneider.com
    Friday, January 6, 2012 7:16 AM
  • Sir would you like to tell me in which forum should I post the same ?
    Friday, January 6, 2012 7:25 AM
  • see the link above that i gave you.  I think you'll be looking for something on quering with Microsoft Access.  There are also books on this topic, and books which have chapters on this topic.
    --rms www.rmschneider.com
    Friday, January 6, 2012 7:29 AM