Getting a simple numbering of records listed based on sort

[zevw]

this is my Query

SELECT [LastName] & ", " & [FirstName] AS Mentor, "Self" AS CheckWritten, "Mentor" AS Title, Sum(CaseFinance.AmtPaid) AS SumOfAmtPaid, Count(*) AS [Counter]
FROM Counselors RIGHT JOIN CaseFinance ON Counselors.CounselorId = CaseFinance.CounId
GROUP BY [LastName] & ", " & [FirstName], "Self", "Mentor"
ORDER BY [LastName] & ", " & [FirstName];

My main issue is the count(*) which gives me a count of how many records each name has. I would like to have a simple numbering the first one should be number 1 the second 2 regardless of how many records they have.

I don't know how to do that.

Thanks in advance

 

[dhookom]

I would create a new query based on the above, saved query and then use a subquery to provide the ranking number.

SELECT *, (SELECT Count(*) FROM qryA A where A.Mentor <=qryA.Mentor) as Rank
FROM qryA

Duane
Hook'D on Access
MS Access MVP