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. Hi Pix, Using of a sub-query to perform a ranking operation on an aggregate field is rather complex.

Jun 17, 2005 Hi, br I would like to include a rank field in a query that sorts descending. I believe the row number would be equal to the rank. For instance, in a query list. I have a report [FacilityIdentifyReportDR] that is fed by a query [QryRPTFacilityIdentityRating02fDR]. I would like to create a field in the query [SVRank] that. 2011 Bmw 335i Xdrive Car And Driver. How to rank records in Microsoft Access. This is the source for the ranking query, which uses a subquery to calculate how many beat the current record.

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 to participate the survey.

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. 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 to participate the survey.