update query - ranking/counter restarting

[matt]

I have a table that looks like this (presorted in
ascending order by Name and descending order by Sales)
with the Rank field empty:

Name Company Sales Rank
a a1 $3
a a2 $2
a a3 $1
b b1 $3
b b2 $2
b b3 $1
c c1 $3
c c2 $2
c c3 $1

I want to run an update query in VBA so that the Rank
field is updated so the table ends up looking like:

Name Company Sales Rank
a a1 $3 1
a a2 $2 2
a a3 $1 3
b b1 $3 1
b b2 $2 2
b b3 $1 3
c c1 $3 1
c c2 $2 2
c c3 $1 3

Thanks for any help!

 

[Nikos Yannacopoulos]

Matt,

the following piece of code will do what you want:

Sub update_sales_rank()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rnk As Integer
Dim pnam As String

strSQL = "SELECT * FROM tblSales ORDER BY Name, Sales DESC"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
pnam = rst.Fields(0)
rnk = 1
Do Until rst.EOF
rst.Edit
If rst.Fields(0) <> pnam Then rnk = 1
rst.Fields(3) = rnk
pnam = rst.Fields(0)
rnk = rnk + 1
rst.Update
rst.MoveNext
Loop

rst.Close
End Sub

Assumptions: table is called tblSales (or change accordingly), Sales field
is numeric or currency.
Notes: you need to go Tools > References in the VB window and add Microsoft
DAO 3.6 Object Library, if not already installed. Also note that the
recordset SQL statement sorts the table as required anyway just to be sure,
as tables do not hold sorting by themselves.

HTH,
Nikos

 

[Matt]

Nikos,

Thanks! This worked like a charm.

-----Original Message-----
Matt,

the following piece of code will do what you want:

Sub update_sales_rank()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rnk As Integer
Dim pnam As String

strSQL = "SELECT * FROM tblSales ORDER BY Name, Sales DESC"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
pnam = rst.Fields(0)
rnk = 1
Do Until rst.EOF
rst.Edit
If rst.Fields(0) <> pnam Then rnk = 1
rst.Fields(3) = rnk
pnam = rst.Fields(0)
rnk = rnk + 1
rst.Update
rst.MoveNext
Loop

rst.Close
End Sub

Assumptions: table is called tblSales (or change accordingly), Sales field
is numeric or currency.
Notes: you need to go Tools > References in the VB window and add Microsoft
DAO 3.6 Object Library, if not already installed. Also note that the
recordset SQL statement sorts the table as required anyway just to be sure,
as tables do not hold sorting by themselves.

HTH,
Nikos




.