DCount("*","YourTable","Region = """ & [Region] & """ and Payment >" &
Payment) + 1
One way is to use the above DCount in a query as a calculated field.
Another method that may be more efficient would be to use a query that
looks like the folowing.
SELECT TA.Region, TA.Payment, Count(Tb.Payment)+1 as Rank
FROM [Yourtable] as TA LEFT JOIN [Yourtable] as TB
ON TA.Region = TB.Region
AND TA.Payment > TB.Payment
GROUP BY TA.Region, TA.Payment
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Hello Evi...that didnt work...basically this is my current table layout with
field names and the ranking column is what I am trying to create
Region Payment Ranking
Los Angeles 500 1
Los Angeles 499 2
Los Angeles 3 4
Los Angeles 345 3
Southern 33 2
Southern 465 1
Southern 23 3
Evi said:
Would this work?
Ranking: DCount("[NumberField]","YourTable","[NumberField]>" &
[NumberField])+1
Evi
RSon said:
Hello,
How do I create a ranking feature in my query. I want to rank the largest
dollar value as number 1, second as 2, 3rd largest as 3.....etc.
Any suggestions??
