Results 1 to 4 of 4
  1. #1
    dolovenature is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    14

    Provide Ranking & Select on the basis of Rank

    Hi everybody,



    Here is my Problem.

    I have a Table ( Table1) with 15 text fields, 5 combo box.

    The table is about the students marks and selection.

    One field is calculating the total marks of the student.

    My problem is

    1) I need to provide Rank to all the 678 students based on total marks.
    2) I need to select Top 5 students from each location. ( there are five different Location Zone)
    3) After selecting these 25 students( 5 from each location) the remaining 653(678-25) students should be ranked again as per total marks.

    How it can be done??

    Regards
    Saikat

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do not use lookups at the table level.
    see
    http://access.mvps.org/access/lookupfields.htm

    Suggest you tell us more about your application.
    Sounds like a design issue if you have 1 table, 678 students, locations and various marks.

    You may wish to research Normalization.

  3. #3
    dolovenature is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    14
    Actually Fields in the Table are

    St_ID, St_Name, Marks_Maths, marks_English, Marks_Geo, Total marks, St_Location.

    I have a form corresponding to this table with the text box...

    Student ID, Name, Marks in Maths, Marks in English, Marks in Geo, Total Marks,

    I have a Combo Box which is selecting the Location of students. There are Four Locations predifined.

    A command button is calculating the Total marks.

    Based on this table and Form what i need to do are..

    1) Selecting top 5 students from each of the four locations based on their total marks.
    2) After selecting those students, remaing students need to be ranked from 1 to .....again based on their total marks.
    3) Finally store the results of the previous two problem in a msexcel sheet.

    Now i am searching the code for implementing that from two command button.

    Regards

    Saikat
    Last edited by dolovenature; 09-11-2012 at 01:05 AM. Reason: information

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I strongly advise you to use a naming convention that does NOT use embedded spaces or special characters in field and object names.

    for TOP x in Group see allen browne's
    http://allenbrowne.com/subquery-01.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Ranking (Look for previous ranking)
    By leobear in forum Queries
    Replies: 3
    Last Post: 01-10-2012, 05:58 PM
  2. How to Use Rank function in MSACCESS
    By kiranmahale in forum Access
    Replies: 3
    Last Post: 12-06-2011, 11:19 PM
  3. please need help RANK
    By Andyjones in forum Queries
    Replies: 8
    Last Post: 09-10-2011, 12:12 AM
  4. Rank Function
    By Dongola in forum Programming
    Replies: 1
    Last Post: 05-07-2011, 06:05 PM
  5. Seeking to pruduce rank by year
    By JLR in forum Queries
    Replies: 0
    Last Post: 01-21-2008, 06:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums