want a new number sequence to match new sort order PLS Help

[beantype]

How can I create a new number sequence (1,2,3,...) that will follow the new
sort order in a table? I need to use the new sequence to increment another
number.

For a field I'll call NEWORDER I have manually assigned specific,
nonsequential numbers for 10 of 200 records, reflecting where I want them to
reside ultimately. I updated the NEWORDER for the remaining 190 records to
their original sort number (Autonumber). Sorted by this NEWORDER all is good.
Even though there are duplicate NEWORDER numbers, they sort properly. All I
need is to generate a new sequence of numbers (1,2,3,...) to match this
order. I tried a couple of make-table & append queries. Is there a way to
create an expressionto create a fill series?

Version Access 2003

 

[beantype]

I'm not succeeding with this approach. I am appending (I don't know how to
append to an empty table) to a new table but the autonumbers are assigned in
the original order, not the new sort order. I even deleted the original AN
field and indexed the new sort order before appending.

Is there a way to fill a field using an expression?
Thanks

 

[beantype]

You'r first response did it for me. I'm not sure but it seems that appending
the query to the new/empty table worked but appending the sorted table did
not."

Thanks alot!