Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New Autonumber field (Access 2002)

    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,

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: New Autonumber field (Access 2002)

    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.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New Autonumber field (Access 2002)

    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.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Autonumber field (Access 2002)

    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,

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: New Autonumber field (Access 2002)

    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>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: New Autonumber field (Access 2002)

    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Autonumber field (Access 2002)

    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •