Ranking by group?

[BiotinX]

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!

[MarvinP]

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

[BiotinX]

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

[MarvinP]

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/

[BiotinX]

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

[MarvinP]

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.

[MarvinP]

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.

[BiotinX]

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!

[alansidman]

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