Ranking

[Germeny Santos]

I have read many posts on this site but cannot find a support to my inquiry.

I have a table named tbl By Category with the following fields:
Category
Subcategory
Supplier
Spend

I need to rank Suppliers by Spend, within the groups:Subcategory and
Category. In other words, I need to have the rank re-stating every time the
Subcategory changes.

Can anybody help?
Thank you



Table: NFL Team Sls

Store Sls Team Rnk
a 100 Eagles 1
a 150 Patriots 2
b 75 Eagles 1
b 300 Patriots 2
c 125 Eagles 1
c 250 Patriots 2


This is what I am looking for.

Note the Rank field is not on the table...I tried to use the following SQL,
but it ranks everyone.

 

[Michel Walsh]

Hi,


You did not supply the SQL statement.

SELECT a.f1, a.f2, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.f1=b.f1 AND a.f2 >= b.f2
GROUP BY f1, f2


will rank for each group f1, accordingly to the value in f2

SELECT a.f1, a.f2, a.f3, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.f1=b.f1 AND a.f2=b.f2 AND a.f3 >= b.f3
GROUP BY f1, f2, f3


will rank for each group f1, f2, accordingly to the value in f3




Hoping it may help,
Vanderghast, Access MVP