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

[Guest]

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

 

[Joseph Meehan]

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

To generate a new autonumber order and have it start from #1 you can
create a new table with your new autonumber field use an update query sorted
in the order you want to populate the new table. Then delete the original
table and rename the new table.

However::::

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

Tables really don't have orders, They are more like a bucket than a
list.

 

[Guest]

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

 

[Joseph Meehan]

I'm not succeeding with this approach. I am appending (I don't know
how to append to an empty table)

Create a query based on the existing table. Add all the fields you
need. In design mode click on query type and pick append then select the
new table as the table to append to.

 

[Guest]

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!

 

[Joseph Meehan]

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!

Glad it worked. Keep in mind what I said about using Autonumbers. They
are not designed for human consumption and they can come back to haunt you
later.