Calculating Rank in Report or Query

[Karrie]

I have a database that tracks the productivity of
employees. I've written a report that groups employees by
functional area and then displays their average
productivity. I do this alphabetically by name.

I want to add a column that displays their ranking
relative to the other employees, but I can't figure it
out. I can do it in Excel using the function Rank, but I
don't see a way to replicate that in Access.

Help! Below is a "sample" of what I'm looking to do.

Name Rank rec/hr
Bob 1 31.56
Jane 3 29.82
Jim 4 28.12
Mary 2 31.00

Thanks

Karrie

 

[Marshall Barton]

I have a database that tracks the productivity of
employees. I've written a report that groups employees by
functional area and then displays their average
productivity. I do this alphabetically by name.

I want to add a column that displays their ranking
relative to the other employees, but I can't figure it
out. I can do it in Excel using the function Rank, but I
don't see a way to replicate that in Access.

Help! Below is a "sample" of what I'm looking to do.

Name Rank rec/hr
Bob 1 31.56
Jane 3 29.82
Jim 4 28.12
Mary 2 31.00

That sample is missing the grouping part so I'll have to be
a little vague. The general idea to do what you want is to
calculate the rank in the report's record source query using
a subquery. I think the query will look something like:

SELECT thetable.*,
(SELECT Count(*)
FROM thetable AS T
WHERE T.[funcarea] = thetable.[funcarea]
AND T.[rec/hr] <= thetable.[rec/hr]
) AS Rank
FROM thetable

You'll have to modify that to use your own table and field
names.