ranking

[benton via AccessMonster.com]

Underneath are the results then followed by the sql statement.I want it to
give me first Ranking for this month(June) and for the last month(May) and
then i need for how many months has a broker held the position of being
number 1,2,3,........


EffMonth Broker APIMvmnt Rank
01/03/2004 100 263040 1
01/03/2004 141 110376 2
01/05/2005 101 101040 3
01/06/2005 10 99408 4
01/04/2005 123 95244 5
01/11/2004 100 93588 6
01/05/2005 121 89352 7
01/11/2004 157 88812 8
01/05/2004 129 87504 9
01/12/2004 106 83544 10

SELECT TOP 10 Prod1.EffMonth, Prod1.Broker, Prod1.APIMvmnt, (select count(
[pROD1].[APImVMNT]) FROM pROD WHERE [APIMvmnt] > [Prod1].[ApiMvmnt] -1) AS
Ranking
FROM Prod AS Prod1
GROUP BY Prod1.EffMonth, Prod1.Broker, Prod1.APIMvmnt
ORDER BY Prod1.APIMvmnt DESC;