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