va*****@mail.com wrote in message

<11**********************@y43g2000cwc.googlegroups .com> :

Arthur

Thanks a lot! This is what I needed.

One more thing - would this solution work if I had an extra field - a

Subterritory so say there are

field Territory Subterritory Value

1000 100 50

1000 100 40

1000 90 30

1000 90 20

1000 90 10

So, the ranking would have to be done against a

Territory-Subterritory combination.

Is there a solution to that?

Thanks again.

This is going to be a drag on recourses, but here's an untested attempt

at SQL. You should be able to find similar if searching for "ranking

query".

select

(select count(*) from mytable s

where s.Territory = t.Territory

and s.Subterritory >= t.Subterritory

and s.[Value] >= t.[Value]) as field1,

t.Territory, t.Subterritory, t.[Value]

from

mytable t

order by

t.Territory, t.Subterritory DESC, t.[Value] DESC

I might have mixed up a bit, if so, try changing the line

and s.Subterritory >= t.Subterritory

to

and s.Subterritory = t.Subterritory

--

Roy-Vidar