+ Reply to Thread
Results 1 to 9 of 9

Ranking by group?

  1. #1
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Ranking by group?

    All -

    I'm trying to rank my students by their grade according to the section they're in. I have no idea where to start!

    Thanks!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,249

    Re: Ranking by group?

    Hi BiotinX,

    I looked for a good website for an answer and didn't find one so I built an example for you to see. If you use Pivot Tables it is a snap! See the attached where I created Group, Student, Score. Then did a Pivot table and using the score as values twice. You click on the second score and show as Rank. KA-BOOM, done! No formulas needed. See the attached.
    Rank Students by Group Example.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Re: Ranking by group?

    You're the best! But....I need to do this in access

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,249

    Re: Ranking by group?

    Sorry, I didn't see the Access part in the forum title. See if this helps:
    https://howtech.tv/office/how-to-cre...rosoft-access/

  5. #5
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Re: Ranking by group?

    Cool! I didn't know you could make pivot tables in access!!!

    I was able to accomplish my goal using the following SQL -
    SELECT A.ALPHA, A.Section, A.Total, Count(*) AS Rank
    FROM Grade_PointSummary AS A INNER JOIN Grade_PointSummary AS B ON (A.Section = B.Section) AND (A.Total <= B.Total)
    GROUP BY A.ALPHA, A.Section, A.Total;

    Where ALPHA is my unique student ID, Section is their class, total is there score, rank is well, their rank, and Grade_PointSummary is a query that has this info in it.

    Thanks!!!

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,249

    Re: Ranking by group?

    GREAT! Glad you solved this problem. The other way is to simply link Excel to your Access data and do it in Excel, but I didn't think you wanted to use Excel after living in Access.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,249

    Re: Ranking by group?

    I always loved Access ability to show the equivalent SQL equivalent. I could build Access Queries much easier than using SQL. So I'd build then in Access and simply use the "Show SQL" from the Access Query dialog. Too bad Access didn't take off like Excel did. Although I'm much better at Excel than Access.

  8. #8
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Re: Ranking by group?

    Thanks for your help!

    My goal is to -
    1) Become more comfortable using access because of its use in managing budgets
    2) Create a grade database that can streamline my department's means of calculating and reporting grades

    I'm getting more comfortable with access, but I still have a lot to learn!

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,260

    Re: Ranking by group?

    Note: Pivot Tables in Access are only available in versions 2010 and earlier. It was removed in 2013. To create a PT in the later versions, export your table or query to Excel or to Power Query and create your PT there.

    Look at this link-->https://www.youtube.com/watch?v=4g67OHYUgF8
    Last edited by alansidman; 04-09-2020 at 10:51 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Ranking within group with multiple tiebreakers
    By ihavequesitons in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2018, 03:46 PM
  2. [SOLVED] Ranking By Group instead of full competition
    By Grimace in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-06-2015, 07:43 PM
  3. [SOLVED] Ranking within a group but also with Tiebreaker
    By ncurran217 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2014, 06:30 PM
  4. [SOLVED] Conditional Ranking/Display top 3 items for each group
    By Novis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-06-2013, 10:34 PM
  5. [SOLVED] Ranking by sub group
    By luke11111 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2013, 07:11 AM
  6. Ranking based on Group
    By ahas_banra in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 11-21-2011, 09:00 AM
  7. ranking within group
    By jojotherider in forum Excel General
    Replies: 6
    Last Post: 12-09-2008, 11:16 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1