New Autonumber field (Access 2002)

[PhilJeary]

I'm sure I looked into this a couple of years ago, but I can't seem to find the results of my search so......

I have a table full of data and I need to add a sequential number to the records, but with the data ordered in a certain way. Is there a way to add a new autonumber field to existing data so the numbers are populated in a user defined order or is my only option looping through the recordset and adding the counter through code? I was hoping adding an autonumber field would work as it seems quicker than looping the recordset.

Regards,

[WebGenii]

The system I'm using uses a table to hold the last number issued. As new numbers are issued an update query revises the number in the table.

[HansV]

Alternatively, you could do the following:
- Select the table in the database window.
- Copy it to the clipboard.
- Paste it, assign a name, and select the Structure Only option.
- Open the copy, and add an AutoNumber field.
- Create a query based on the original table.
- Add all fields, and specify the sort order you want.
- Select Query | Append Query.
- Specify the copied table as target.
- Check that all fields are assigned correctly, and make sure there are no duplicate assignments.
- Run the append query.

[PhilJeary]

In which case i'll do it in a loop as appending 32000 records will probably be slower than updating 32000 records. I should have mentioned that this will be part of a function rather than a once off update!

Thanks for your replies.

Regards,

[WebGenii]

Gee - look at the high quality of the answer you get, when you stop to actually read the question properly. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[MarkLiquorman]

You said you wanted to assign the autonumbers to the data "...with the data ordered in a certain way". While this would be fine for the existing records, what about new records? Whatis the purpose of this new autonumber field anyway. If you can order the data in a certain way already, why do you need the autonumber?

[PhilJeary]

quote: "Gee - look at the high quality of the answer you get, when you stop to actually read the question properly"
Lol, when I first read it back it made sense, it was only after I went back to it that I noticed a few holes in my sentence!

I'm not sure what its like elsewhere in the world but in the UK we have a postal system called mailsort for large batches of mail where we can number the envelopes in a certain way so that the mail can be put into bags so they skip the posts sorting office. The are ordered by a field with a letter in it first (R, D or N) then a number. They must stay in order as groups of numbers need to go into bags together.

To make it a bit easier for our mailing centre to find items when they mess things up (someone knocks a stack off a desk for example) they'd like a running number through the data. Therefore, after the data has been populated with its R, D & Ns and has its mailsort numbers it then needs to be sorted by those fields and have an incremental number added to it.

I already had code to add in fields so I planned on adding another line to add an autonumber field, but I couldnt get it to be added with the data ordered in my specified order, hence my post.

Based on the first couple of replies I added the loop method in last night and it seems to work okay. It may be a little slower than adding an autonumber field especially for recordsets over 30,000 but at the least, it works.

Just to round things off, no new records will be added to the recordset once it is populated with its mailsort details.

Regards,