Ranking Line items on a report

[Guest]

I have a form that shows the results of an athletic event with breakdown by
age, skill level,etc. It is sorted on the report by score within the various
groupings and it is obvious that the highest score is first place, second
highest score is 2nd ...

but the user wants to see a 1 after the highest score, a 2 after th second
highest, etc.

I could use one of several ranking routines I have gotten over the years
from this group to create the rank indicator in the recordsource query but I
would perfer to do this at the report level. Can it be done??

thanks for any and all help

 

[Marshall Barton]

I have a form that shows the results of an athletic event with breakdown by
age, skill level,etc. It is sorted on the report by score within the various
groupings and it is obvious that the highest score is first place, second
highest score is 2nd ...

but the user wants to see a 1 after the highest score, a 2 after th second
highest, etc.

I could use one of several ranking routines I have gotten over the years
from this group to create the rank indicator in the recordsource query but I
would perfer to do this at the report level. Can it be done??

If there will never be any ties in the ranking, you can just
use a running sum text box with the expression =1.

For a true ranking that allows for ties, either use a
subquery in the record source query or use a DCount function
in a report text box. Using DCount is probably (a lot?)
slower because it may have to be done multiple times for at
least some records).

 

[Guest]

thanks much. The running sum will work because i use another field to break
ties.

Is other apps I have used the DCount function in queries to rate/rank values
but have not used it in a Report text box. Unless I mis-read your post you
are suggesting I use a subquery to rank/rate without using the DCount
function but I have no idea how to do that. Do you have any sample queries
that rank/rate without the DCount?

thanks again

 

[Marshall Barton]

thanks much. The running sum will work because i use another field to break
ties.

Is other apps I have used the DCount function in queries to rate/rank values
but have not used it in a Report text box. Unless I mis-read your post you
are suggesting I use a subquery to rank/rate without using the DCount
function but I have no idea how to do that. Do you have any sample queries
that rank/rate without the DCount?

Here's an air code example using a subquery:

SELECT f1,f2,f3,
(SELECT Count(*)
FROM table As X
WHERE X.f2 < T.f 2
OR (X.f2 = T.f3 And X.f3 < T.f3)
) As Rank
FROM table As T

f2 is the ranking field and f3 is the tie breaker field.

As you can see, the subquery can be replaced with a DCount
quite easily.

There's also a way to do that using a non-equi join to a
virtual table that would probably be a little faster, but
non-equi joins can not be done in the QBE, only in SQL View.

The potential issue with using a subquery is that the
resulting dataset might(?) not be updatable. For no good
reason that I can understand, the DCount approach is
required in an UPDATE query.