updating a sorted numerical field to absolute position in recordset?

[gerardianlewis]

Suppose you have an auxiliary table that looks like this:

IdWidget(Autonumber, primary key)
Widget (text)
Some other fields
SortOrder (Long Integer)

Your application first empties the table, then fills it with records,
The SortOrder field at this point contains zeroes. In response to user
interaction, this field is then updated with a series of values based
on some complex calculations involving values in other tables. These
resulting SortOrder numbers reflect the order that the records should
be presented in, but for various reasons they are not consecutive. For
example:

Widget SortOrder
aabb 2
xxcc 3
aaxx 54
ddee 244

etc.

Now, obviously a simple SORT BY SortOrder will present the resultset
in the correct order, but for various boring reasons I want to be able
to update the contents of the SortOrder field so that the numbers
start at 1 and are consecutive, like so:

Widget SortOrder
aabb 1
xxcc 2
aaxx 3
ddee 4

The easiest way to do this is to run a series of UPDATE queries inside
a loop to update the records one at a time, feeding the loop counter
through as a parameter. But this is slow and inefficient. It strikes
me there ought to be a way to do the entire update in one query. As an
experiment, I wrote a SELECT query that presented the numbers I wanted
to poke into the SortOrder field, and it worked fine: I simply added,
inside my main query, a calculated field containing a SELECT statement
like this (I'm omitting the brackets for clarity):

MyCalcField: (SELECT Count(w.Widget) FROM Widgets AS w WHERE
w.SortOrder <= Widgets.SortOrder)

In other words, for each record, simply add up the number of records
in the same table where SortOrder <= the SortOrder of the current
record. Unfortunately, while this works for a SELECT, it doesn't work
for an UPDATE: if you turn this SELECT into an update, Access returns
a message saying something like "you need to use an updateable query"
and refuses to execute it. I tried rewriting the update query using
DCount(widget) etc, but while the query executed without problems, the
results weren't as expected: some ordinals were missed out and others
were duplicated, presumably because the calculation of DCount isn't
done in advance but is alternated with the updating.

Am I going to have to add a new field to the table to store
intermediate values and run two queries (one to poke the ordinals into
MyUglyTempField and the other to copy them over to SortOrder), or is
there some more elegant method I haven't thought of?

TIA

 

[Evi]

I use recordset code to do this. I run it by pressing a button in my form.
Mine looks like this

Private Sub cmbRenumberTracks_Click()
Dim a As Integer
Dim MyTable As String
Dim Db As Database
Dim rs As Recordset

MyTable = "QryRenumberTracks"
'This query is sorted in Num order
'Num is a number field
'(though not a primary key field)
Set Db = CurrentDb
Set rs = Db.OpenRecordset(MyTable, dbOpenDynaset)
a = 1
rs.MoveFirst
'go to first record in MyTable
Do Until rs.EOF
'stop when you get to the end of MyTable
rs.Edit
rs!Num = a
'Update my Num field
rs.Update
rs.MoveNext
a = a + 1
Loop

rs.Close
Set rs = Nothing
Set Db = Nothing
End Sub

Evi

 

[gerardianlewis]

I use recordset code to do this. I run it by pressing a button in my form.
Mine looks like this

Private Sub cmbRenumberTracks_Click()
Dim a As Integer
Dim MyTable As String
Dim Db As Database
Dim rs As Recordset

MyTable = "QryRenumberTracks"
'This query  is sorted in Num  order
'Num is a number field
'(though not a primary key field)
Set Db = CurrentDb
Set rs = Db.OpenRecordset(MyTable, dbOpenDynaset)
a = 1
rs.MoveFirst
'go to first record in MyTable
Do Until rs.EOF
'stop when you get to the end of MyTable
        rs.Edit
        rs!Num = a
    'Update my Num field
        rs.Update
        rs.MoveNext
        a = a + 1
Loop

rs.Close
Set rs = Nothing
Set Db = Nothing
End Sub

Yes, well, that's close to what I already had, an update inside a loop
- except I use ADO.NET rather than DAO to communicate with the DB. (I
liked DAO better, it was more flexible and I suspect a lot faster, but
it's not being supported any more, so...). As I said, I'm looking for
a solution that doesn't use a loop in code, as when you're updating
several thousand records, let alone tens of thousands, the
sluggishness starts to show. If no one else replies I'll probably go
for the two-stage solution using an extra field in the table for the
purpose.

Thanks for replying.