none
Ranking totals in a report RRS feed

  • Question

  • In an Access database I have a report which displays a list of students and the totals marks they made in a test. How could I rank them based on the total while taking into consideration that there could be a tie. For instance if there is a tie for third I will like to display the ranking as 1, 2, 3, 3, 5 etc. Thank you.

    Gordon Swan

    Monday, February 19, 2018 2:11 AM

Answers

  • As I said before, join the two queries in a third query on StudentID:

    SELECT YourCrosstabQuery.*, YourRankingQuery.Rank
    FROM YourCrosstabQuery INNER JOIN YourRankingQuery
    ON YourCrosstabQuery.YearClassStudentID = YourRankingQuery.YearClassStudentID;

    Ken Sheridan, Stafford, England

    • Marked as answer by Gordon Swan Wednesday, February 28, 2018 10:48 PM
    Wednesday, February 28, 2018 12:32 PM

All replies

  • Ranking rows on the basis of a value in a column is computed by returning the number of rows +1 where the value is greater than other rows in the set.  This can be done most efficiently by a JOIN of two instances of the table in question, or less efficiently, by means of a subquery.

    Where the values are aggregated, as in your case, to return the ranks in a single query would mean joining subqueries in which the aggregation is undertaken, or, where a subquery solution is used, by nesting the subqueries, e.g.

    SELECT StudentID, TotalMark,
         (SELECT COUNT(*) + 1
         FROM (SELECT  StudentID, SUM(Mark) AS TotalMark
                      FROM StudentMarks
                      GROUP BY StudentID) AS T2
         WHERE T2.TotalMark > T1.TotalMark) AS Rank
    FROM (SELECT  StudentID, SUM(Mark) AS TotalMark
                  FROM StudentMarks
                  GROUP BY StudentID) AS T1
    ORDER BY TotalMark DESC;

    For a report's RecordSource you can exclude the ORDER BY clause of course, as reports are ordered by their internal sorting an grouping mechanism

    You'll find examples of row numbering and ranking queries in RowNumbering.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file does not currently include any queries for ranking by aggregated values, though I might well add some.  The equivalent of the above query if used in my demo would be:

    SELECT CustomerID, TotalAmount,
         (SELECT COUNT(*) + 1
         FROM (SELECT  CustomerID, SUM(TransactionAmount) AS TotalAmount
                      FROM Transactions
                      GROUP BY CustomerID) AS T2
         WHERE T2.TotalAmount > T1.TotalAmount) AS Rank
    FROM (SELECT  CustomerID, SUM(TransactionAmount) AS TotalAmount
                  FROM Transactions
                  GROUP BY CustomerID) AS T1
    ORDER BY TotalAmount DESC;

    PS:  The equivalent in my demo, using a JOIN, would be:

    SELECT T1.CustomerID, T1.TotalAmount,
    COUNT(T2.CustomerID)+1 AS Rank
    FROM (SELECT  CustomerID, SUM(TransactionAmount) AS TotalAmount
                  FROM Transactions
                  GROUP BY CustomerID) AS T1
    LEFT JOIN (SELECT  CustomerID, SUM(TransactionAmount) AS TotalAmount
                      FROM Transactions
                      GROUP BY CustomerID) AS T2
    ON T2.TotalAmount >T1.TotalAmount
    GROUP BY T1.CustomerID, T1.TotalAmount
    ORDER BY T1.TotalAmount DESC;


    Ken Sheridan, Stafford, England



    Monday, February 19, 2018 1:39 PM
  • Another simple method uses the Serialize function

    See this post for more info and examples

    https://www.access-programmers.co.uk/forums/showthread.php?t=297922
    • Edited by isladogs52 Tuesday, February 20, 2018 2:25 AM
    Tuesday, February 20, 2018 2:24 AM
  • I am using this crosstab query to display the term report for a class of sudents:

    TRANSFORM First(qryClassTermReport.Mark) AS FirstOfMark
    SELECT qryClassTermReport.FirstName, qryClassTermReport.LastName, qryClassTermReport.[Class Teacher], qryClassTermReport.YearClassName, qryClassTermReport.SchoolTerm, qryClassTermReport.EndDate, Sum(qryClassTermReport.Mark) AS Total, Count(qryClassTermReport.SubjectAbbrv) AS Subjects
    FROM qryClassTermReport
    GROUP BY qryClassTermReport.FirstName, qryClassTermReport.LastName, qryClassTermReport.[Class Teacher], qryClassTermReport.YearClassName, qryClassTermReport.SchoolTerm, qryClassTermReport.EndDate
    PIVOT qryClassTermReport.SubjectAbbrv In ("Agri Sci","Lang Arts","Maths","Phy Ed","Sci","Soc Dev","Soc Stu","Span","Int Arts");

    Result:

    I then hen changed the queryto look like this to rank by total:

    TRANSFORM First(qryClassTermReport.Mark) AS FirstOfMark
    SELECT T1.FirstName, T1.LastName, T1.[Class Teacher], T1.YearClassName, T1.SchoolTerm, T1.EndDate, T1.Total, T1.Subjects, Count(T2.Total)+1 AS Rank
    FROM (SELECT qryClassTermReport.FirstName, qryClassTermReport.LastName, qryClassTermReport.[Class Teacher], qryClassTermReport.YearClassName, qryClassTermReport.SchoolTerm, qryClassTermReport.EndDate, Sum(qryClassTermReport.Mark) AS Total, Count(qryClassTermReport.SubjectAbbrv) AS Subjects FROM qryClassTermReport GROUP BY qryClassTermReport.FirstName, qryClassTermReport.LastName, qryClassTermReport.[Class Teacher], qryClassTermReport.YearClassName, qryClassTermReport.SchoolTerm, qryClassTermReport.EndDate)  AS T1
    LEFT JOIN (SELECT qryClassTermReport.FirstName, qryClassTermReport.LastName, qryClassTermReport.[Class Teacher], qryClassTermReport.YearClassName, qryClassTermReport.SchoolTerm, qryClassTermReport.EndDate, Sum(qryClassTermReport.Mark) AS Total, Count(qryClassTermReport.SubjectAbbrv) AS Subjects FROM qryClassTermReport GROUP BY qryClassTermReport.FirstName, qryClassTermReport.LastName, qryClassTermReport.[Class Teacher], qryClassTermReport.YearClassName, qryClassTermReport.SchoolTerm, qryClassTermReport.EndDate)  AS T2
    ON T2.Total > T1.Total
    GROUP BY T1.FirstName, T1.LastName, T1.[Class Teacher], T1.YearClassName, T1.SchoolTerm, T1.EndDate, T1.Total, T1.Subjects

    PIVOT qryClassTermReport.SubjectAbbrv In ("Agri Sci","Lang Arts","Maths","Phy Ed","Sci","Soc Dev","Soc Stu","Span","Int Arts");

    Result:

    I want to show the marks for each subject except Soc. Dev which was not done. How can this be corrected?



    • Edited by Gordon Swan Monday, February 26, 2018 12:39 PM
    Monday, February 26, 2018 12:29 PM
  • Rather than trying to do everything in one query, I'd suggest that you create a separate query which returns the total marks and rank per student, using one of the methods I described earlier, preferably that using a JOIN, which is more efficient.  Add the StudentID or whatever is the key, to your original crosstab query, and be sure to return it in the ranking query also.

    Then join the crosstab query to the ranking query on StudentID and base the report on this new query, sorting the report in ascending order by the rank column.  The ORDER BY clause in the ranking query can be omitted of course, as the order in which the rows are returned in the report is governed by its internal sorting and grouping mechanism.

    Ken Sheridan, Stafford, England

    Monday, February 26, 2018 1:32 PM
  • This is my Crosstab query:

    TRANSFORM First(qryClassTermReport.Mark) AS FirstOfMark
    SELECT qryClassTermReport.YearClassStudentID, qryClassTermReport.FirstName, qryClassTermReport.LastName, qryClassTermReport.[Class Teacher], qryClassTermReport.YearClassName, qryClassTermReport.SchoolTerm, qryClassTermReport.EndDate, Sum(qr and rankyClassTermReport.Mark) AS Total, Count(qryClassTermReport.SubjectAbbrv) AS Subjects
    FROM qryClassTermReport
    GROUP BY qryClassTermReport.YearClassStudentID, qryClassTermReport.FirstName, qryClassTermReport.LastName, qryClassTermReport.[Class Teacher], qryClassTermReport.YearClassName, qryClassTermReport.SchoolTerm, qryClassTermReport.EndDate
    PIVOT qryClassTermReport.SubjectAbbrv In ("Agri Sci","Lang Arts","Maths","Phy Ed","Sci","Soc Dev","Soc Stu","Span","Int Arts");

    And this is the query that returns the total marks and rank:

    SELECT T1.YearClassStudentID, T1.Total, COUNT(T2.YearClassStudentID)+1 AS Rank
    FROM (SELECT YearClassStudentID, SUM(Mark) AS Total FROM tblSubjectGrades GROUP BY YearClassStudentID)  AS T1
    LEFT JOIN (SELECT YearClassStudentID, SUM(Mark) AS Total FROM tblSubjectGrades GROUP BY YearClassStudentID)  AS T2
    ON T2.Total > T1.Total
    GROUP BY T1.YearClassStudentID, T1.Total
    ORDER BY T1.Total DESC;

    I will like to get some assistance in doing the join. Thank you.

    Wednesday, February 28, 2018 12:03 AM
  • As I said before, join the two queries in a third query on StudentID:

    SELECT YourCrosstabQuery.*, YourRankingQuery.Rank
    FROM YourCrosstabQuery INNER JOIN YourRankingQuery
    ON YourCrosstabQuery.YearClassStudentID = YourRankingQuery.YearClassStudentID;

    Ken Sheridan, Stafford, England

    • Marked as answer by Gordon Swan Wednesday, February 28, 2018 10:48 PM
    Wednesday, February 28, 2018 12:32 PM