Results 1 to 9 of 9
  1. #1
    charly.csh is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    186

    Create a list with no Autonumber

    Hi dear friends from Access



    I am transfering a database created in excel to my access database. The first field I inserted is an ID field which is an Autonumber (for now) however some of this records should be deleted further from the list and new ones will be pasted

    In my excel file I have a continuous list that is updated with =B"#"+1 and when a record is deleted I just do an update of the formula and then I have a new consecutive number list again (If I just delete the field in access I will lose the consecutive number I had)

    Does somebody now how can I do something similar with my Access Database?


    (Sometimes I get new contacts for sales and some of them are not interesed on the service should be deleted and the dabases come from Facebook "friends" and Google contacts)

    Thanks in advance!!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    what is so important about keeping a continuous list of numbers? autonumbers are about uniquely identifying a record, not about maintaining consecutive list. If you renumber then the record with a number 20 yesterday becomes 19 today when you delete record 19. So all the other tables that were linked on 20 will now be linked to the wrong record

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree with Ajax. If a numbered list is important, then that is, in most cases, a separate field. Autonumbers are usually very helpful and advisable, but not for the reason you desire.
    Read about autonumbers

    http://www.utteraccess.com/wiki/Autonumbers
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    charly.csh is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    186
    Understand the point with the autumber. How do you add a new field like this? or what should be the logic sequence of this?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Add a field like what? Autonumber?
    Not sure what to say because you didn't answer the question as to why you need a consecutively numbered list. Can tell you that if you apply Excel logic to a database you will cause yourself a lot of grief. Have you familiarized yourself with db normalization?

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by charly.csh View Post
    Understand the point with the autumber. How do you add a new field like this? or what should be the logic sequence of this?
    Create a query based on your table and add a new field in this query with the expression below:
    Code:
    SN: DCount("ID","YouTableName","ID<" & [ID])+1
    For example, in SQL view:
    Code:
    SELECT DCount("ID","YouTableName","ID<" & [ID])+1 AS SN, Field1, Field2, Field3 FROM YouTableName;
    (change the "YouTableName" with the actual name of your table)

    Keep the actualy ID for the database and the SN only for your eyes.

    Cheers,
    John

  7. #7
    charly.csh is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Nov 2014
    Posts
    186
    Excelleeeent!!!!
    That was exactly what I wanted!!! Thank you very much for the Help John!!!!

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    You're welcome Charly!

    I'm happy to help!

    Cheers,
    John

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    It works but domain aggregate functions can cause slow performance in queries (a nested subquery can do this calc as well). Another approach is to use RunningSum property of textbox in report.

    Keep in mind with query solution, if records are sorted by a field other than ID, the numbers will likely not be sequential. With the report, numbers will associate with different records.

    So, I'll ask as well - what purpose does this number serve?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-26-2014, 11:28 PM
  2. How to Create a Autonumber with dashes
    By sylviar in forum Access
    Replies: 5
    Last Post: 05-09-2013, 09:08 AM
  3. Replies: 6
    Last Post: 04-27-2012, 05:14 PM
  4. Replies: 30
    Last Post: 01-16-2012, 05:49 PM
  5. CREATE TABLE and AutoNumber fields
    By JTeagle in forum Queries
    Replies: 1
    Last Post: 11-10-2011, 03:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums