Average Rank query

[gcgoode]

Right now, I have an output that looks like this:

AvgCost   CompanyID         Policy#
25              22                    12
28              23                    12
35              24                    12
21              25                    12
20              22                    20
15              24                    20
13              23                    21
43              24                    21

Etc.

I want to know if it is possible to get an output that adds a ranks the CompanyIds by average cost per each policy #.


I really am just interested in how companyID24 ranks, and I have the query set up where it only outputs Policy#'s that company 24 is ranked in (has sold product in).

So in the probable case that was difficult to understand, here is what I am wanting.

Output I have been working to achieve (but having struggles):

AvgCost   CompanyID         Policy#         Rank
25              22                    12              2
28              23                    12              3
35              24                    12              4
21              25                    12              1
20              22                    20              2
15              24                    20              1
13              23                    21              1
43              24                    21              2

Ideal output, that I have no clue on how to do:

AvgCost   CompanyID         Policy#         Rank
35              24                    12              4
15              24                    20              1
43              24                    21              2

Any help would be greatly appreciated.

Thank you.

[ranman256]

1 way is:
to do a make table query, of your avgs sorted 1 to N
then add [RANK] field as (autonumber). this will instantly set the rankings. 1 thru N.

[gcgoode]

1 way is:
to do a make table query, of your avgs sorted 1 to N
then add [RANK] field as (autonumber). this will instantly set the rankings. 1 thru N.

Wouldn't that just rank the Cost, without regard to the policy#?

[ranman256]

the records go in whatever sort you give them. If you sort Avg, they will be in Avg order in the table and stay that way when you add the autonumber.
but if you want different #1 for different companies, then this wont work.

[ranman256]

here is some code that will scan thru a query, and set the 'ranking' field.
You provide the query, sorted as needed, the group and the field to write the rank# to.
If you use various Groups to rank, it resets on that group. So the query must sort by: group, Value (descending)
give: query, groupField, RankField

usage:
RankByGrp "qsTopSales","EmpID","RANK"

  'rank items in query, break on GROUPBY
Public Sub RankByGrp(pvQry, ByVal pvGroupBy, ByVal pvRankFld)
'pvQry : query name, sorted by GroupBy then value# to rank
'pvGroupBy : field to group the rankings on.  Rank resets to 1, when the Group changes
'pvRankFld : fieldname to write the rank#


Dim rst   'As Recordset
Dim lNum As Long
Dim vPrevGrp, vGrp


lNum = 1
Set rst = CurrentDb.OpenRecordset(pvQry)
With rst
    While Not .EOF
         vGrp = .Fields(pvRankFld).Value & ""
         If vGrp <> vPrevGrp Then lNum = 1
         
         .Fields(pvRankFld) = lNum
         .Update
         lNum = lNum + 1
         
         .MoveNext
          vPrevGrp = vGrp
    Wend
End With
Set rst = Nothing
End Sub

[gcgoode]

Thank you for this help. Excuse my ignorance, but I'm having a bit of trouble with how I use the code.

Do I still need to make my query into a table, add the autonumber field, and then make a query based of that table? And what query name is going in that first position where you have qsTopSales, the first one?

[ranman256]

like I said, if you are using only 1 rank for all records, use the autonumber trick.
but
if you are ranking say, clients in a company, but the data has many companies, then use the code.

Im still not clear on what you want ranked. policies / company?

[ranman256]

build a query 'qsTopPolysByCo' with the sort order
select * from qsAvg order by CompanyID, AvgCost

put a button on a form
in the button click event run:

RankByGrp "qsTopPolysByCo","CompanyID","RANK"

[gcgoode]

Sorry about the confusion, I just meant I was confused about how to use the code.

And I want to rank the CompanyID's by my cost field per each Policy#.

[ItsMe]

@ranman
I think you are missing a .Edit in your VBA example. Also, you might want to add a .MoveFirst

[ranman256]

MoveFirst is not needed.
its the default. (it doesnt start in the middle)

[ItsMe]

MoveFirst is not needed.
its the default. (it doesnt start in the middle)

I believe you are correct. That is why I used the word "might". I believe the code there will default to a "Forward Only" recordset which places the cursor at the first record.