# Ranking by group?

1. ## 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. ## 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

3. ## Re: Ranking by group?

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

4. ## 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. ## 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. ## 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. ## 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. ## Re: Ranking by group?

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. ## 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.

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

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