Record number

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

I am trying to get the record numbers to display as an
expression. So if Joe Blow is record 5, an expression in
the query displays "5" for Joe.

The query totals monthly sales by salespeople. It
displays in DSC order based on SumOfAmount and is
restricted to the top 10.

So the first record is the top salesperson (therefore
current ranking is #1) and the 10th record is the 10th
place salesperson (or the 10th record).

This seems pretty straight forwards, but I just can't
seem to figure it out.

Thank you in advance for suggestions.

Ron
 
Dear Ron:

The technique I'll show you for doing this is called a "correlated
subquery."

It is basically a subquery that counts the number of rows that rank
ahead of the current row. Just add 1 to this number to produce
ordinary "ranking" values.

Assuming you have a query that returns SalesPerson and SalesAmount
called "YourQuery":

SELECT SalesPerson, SalesAmount,
(SELECT COUNT(*) + 1 FROM YourQuery Q1
WHERE Q1.SalesAmount > Q.SalesAmount) AS Rank
FROM YourQuery Q
ORDER BY SalesAmount DESC

This query, adapted to your situation, should do it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hurray! Thanks, Tom!

I used your ( ..) code in combination with setting
the "alias" and BINGO!!

I had been scratching my head on this one for days, and
I'm glad to have it out of the way.


Ron
 
Back
Top