locked
Ranking Aggregate Field in Access Query RRS feed

  • Question

  • I am trying to rank an aggregate field in access but my efforts are in vain with errors based on referencing. I am ranking using a subquery but the problem comes about due to the alias names resulting from performing an average on a field. The code is as below:
    SELECT [Exams].[StudentID], Avg([Exams].[Biology]) AS [AvgBiology], (SELECT Avg(T.Biology) AS [TAvgBiology], Count(*) FROM [Exams] AS T WHERE T.[TAvgBiology] > [AvgBiology]) + 1 AS Rank
    FROM [Exams]
    GROUP BY [Exams].[StudentID]
    ORDER BY Avg([Exams].[Biology]) DESC;
    Errors that come about state: "You have selected a subquery that can return more than one value blah blah...please use the Exist keyword.. ". From the code above I think you get the gist of what I am trying to achieve. Any help is appreciated.
    Sunday, February 15, 2015 2:53 PM

Answers

  • I'd do it in two steps:

    1) Create a query qryAvgBiology:

    SELECT StudentID, Avg(Biology) AS AvgBiology
    FROM Exams
    GROUP BY StudentID

    2) Create a query based on qryAvgBiology:

    SELECT StudentID, AvgBiology, 
      (SELECT Count(*) FROM qryAvgBiology AS Q WHERE Q.AvgBiology > qryAvgBiology.AvgBiology)+1 AS Rank
    FROM qryAvgBiology


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 15, 2015 3:14 PM
  • Hi Pix,

    Using of a sub-query to perform a ranking operation on an aggregate field is rather complex. As I tested, the reply from Hans could resolve your original issue. Of course, you could also use your original join statement to achieve your goal.

    >> I think access should integrate a RANK function as in MS Excel.

    I am afraid that there is no Rank function in MS Access currently. If you need this function in future versions of Access, I would recommend you submitting a feedback.


    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, February 16, 2015 8:40 AM

All replies

  • I'd do it in two steps:

    1) Create a query qryAvgBiology:

    SELECT StudentID, Avg(Biology) AS AvgBiology
    FROM Exams
    GROUP BY StudentID

    2) Create a query based on qryAvgBiology:

    SELECT StudentID, AvgBiology, 
      (SELECT Count(*) FROM qryAvgBiology AS Q WHERE Q.AvgBiology > qryAvgBiology.AvgBiology)+1 AS Rank
    FROM qryAvgBiology


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 15, 2015 3:14 PM
  • I did it that way previously but considering the amount of queries of this nature I will be having does not go well by me. Is there any way to include it all in one query. Thanks for the reply Hans.

    NB: Most of my queries will not use the actual values but will be performing different forms of performance analysis with ranking involved. An all inclusive method will be 'life saving'.


    • Edited by Pix Elliot Sunday, February 15, 2015 3:26 PM
    Sunday, February 15, 2015 3:25 PM
  • Try this then:

    SELECT StudentID, AvgBiology, (SELECT Count(*) FROM (SELECT StudentID, Avg(Biology) AS AvgBiology FROM Exams GROUP BY StudentID) AS Q WHERE Q.AvgBiology>qryAvgBiology.AvgBiology)+1 AS Rank
    FROM (SELECT StudentID, Avg(Biology) AS AvgBiology FROM Exams GROUP BY StudentID) AS qryAvgBiology


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, February 15, 2015 3:58 PM
  • Given that my original join statement looks this long:
    FROM ([StudentsC1T8] INNER JOIN [ExamsClass1T3)] ON [StudentsClass1T8)].[AdmissionNo] = [ExamsClass1T3].[AdmissionNo]) LEFT JOIN [C1T3SR-DEV] ON [StudentsClass1T8].[ClassID] = [C1T3SR-DEV].[ClassID]
    I am concluding that the use of a sub-query to perform a ranking operation on an aggregate field is rather complex; leaving the only (one-query) option to be limited to the use of DCount function (much slower). I think access should integrate a RANK function as in MS Excel. Thanks again Hans.

    Sunday, February 15, 2015 5:14 PM
  • Hi Pix,

    Using of a sub-query to perform a ranking operation on an aggregate field is rather complex. As I tested, the reply from Hans could resolve your original issue. Of course, you could also use your original join statement to achieve your goal.

    >> I think access should integrate a RANK function as in MS Excel.

    I am afraid that there is no Rank function in MS Access currently. If you need this function in future versions of Access, I would recommend you submitting a feedback.


    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, February 16, 2015 8:40 AM