Calculate rank based upon multiple fields

[Guest]

Table: top25
fields: premium, office, segment

I want add a rank score to show the top 25 offices per segment in terms of
the most premium. Some offices will have only a few segments, some have
hundreds, all I want is the top 25. For each segment the rank has to start
over. So segment 1, 25 results, segment 2, 25 results and so on.

please advise,
Dan

 

[Guest]

It can be done in multiple steps and multiple loop methods. First create a
query say, query1, that sort office in segment 1 in descending order. afther
that run append query one record at a time for 25 times.

The vb code will look like this (make modifications when necessary):

function rank()

set db = currentdb()
set rs = db.openrecordset("query1")

rs.movefirst
x = 1

for x = 1 to 25

rssql = "Insert Into NewTable (Office, Segment, Rank) " & _
"select query1.office, query1.segment, " & x & " As Rank " & _
"From query1"

docmd.runsql rssql

next x

rs.close
set db = nothing
end function

do these multiple times for different segments.

This is just the method you can use, the sql, query or the syntax may need
to be modified.

hope this helps.