Ranking query

[Leslie Isaacs]

I have a query (SQL below) in which I need to rank each [prac name]
according to the value in [fee per message].
I know this can be done, but cannot recall how.
Hope someone can help.

Many thanks
Les

My query:

SELECT practices.[prac name], Count(messages.log) AS CountOflog,
practices.newfee, [newfee]/Count([messages]![log]) AS [fee per message]
FROM practices INNER JOIN messages ON practices.[prac name] =
messages.[message practice]
WHERE (((messages.[logged when])>#4/1/2003#) AND ((messages.payrunmonth) Is
Null))
GROUP BY practices.[prac name], practices.newfee, practices.live
HAVING (((practices.[prac name])<>"PayeDoc") AND ((practices.newfee)>0) AND
((practices.live)=True))
ORDER BY [newfee]/Count([messages]![log]);

 

[Tom Ellison]

Dear Leslie:

Let's say the query you posted is saved as MyQuery. Then:

SELECT [prac name], CountOflog, newfee, [fee per message],
(SELECT COUNT(*) + 1 FROM MyQuery Q1
WHERE Q1.[fee per message] > Q.[fee per message]
FROM MyQuery Q
ORDER BY [fee per message] DESC

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts