Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Aug 2004
    Posts
    362

    How to auto-increment a field by number?

    Hello,

    How can I design a query which auto-increments a field by number?

    Im currently using an autonumber and am aware that autonumbers once a record is deleted carry on from the last number eg 1,2,3 if 3 was deleted the next autonumber wont be 3 it will be 4.

    So I need to find another way of using a field to always auto increment 1,2,3 and so on, and if 3 is deleted, the next record will be 3.

    I will keep the autonumber field purely for use by the database.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Check out DMax()
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Aug 2004
    Posts
    362
    I need to be able to insert a starting number in the first empty field (called "CurrentID") of my table (not the auto number field), and then for every new record which is added to the table, have the field CurrentID increased by 1.

    Can it be done just using a query?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by moss2076 View Post
    I will keep the autonumber field purely for use by the database.
    then why not just use an actual autonumber?

    seems like you're going to a lot of trouble to avoid gaps in the number sequence when the gaps don't really matter at all anyway

    why do you need them consecutive?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2004
    Posts
    362
    Because the person who needs the figures has asked for there not to be any gaps...Im just doing what Im asked! Plus it would be good for my knowledge to know how to do it.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by moss2076 View Post
    Because the person who needs the figures has asked for there not to be any gaps...
    that person perhaps did not explain the real requirements to you properly, or perhaps you misunderstood the requirements

    my advice: go back to that person, and explain that the gaps will not hurt anybody in any way, and that setting up a no-gaps number is not only inefficient and unnecessary, but can actually lead to errors

    also, please give that person the link to this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Posts
    362
    Quote Originally Posted by r937 View Post
    that person perhaps did not explain the real requirements to you properly, or perhaps you misunderstood the requirements

    my advice: go back to that person, and explain that the gaps will not hurt anybody in any way, and that setting up a no-gaps number is not only inefficient and unnecessary, but can actually lead to errors

    also, please give that person the link to this thread
    No you are not listening to me. I would like to know how to auto increment numbers, even if a row is deleted and there are gaps in an autonumber.

    That is all Im asking, is it that difficult?

    I dont need to go back to anybody, I dont need to give anyone the link to this thread, I just need help with the question I asked thats all.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by moss2076 View Post
    is it that difficult?
    yes, it is

    not only is it exceedingly difficult, it is also unnecessary

    if you would take a moment and explain why you need there to be no gaps, then perhaps we can move forward

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2004
    Posts
    362
    Quote Originally Posted by r937 View Post
    yes, it is

    not only is it exceedingly difficult, it is also unnecessary

    if you would take a moment and explain why you need there to be no gaps, then perhaps we can move forward

    Ok why didnt you just say it is exceedingly difficult in the first place?

    The number with no increments will be merged into another field consisting of letters to create a new set of numbers which will go into another table.

    Trust me, I wouldnt ask the question in the first place if it wasnt required. Plus the person who will be actually using the databse has said they will find it better for them also. I dont want to be a time waster...

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's the scenario

    say you have this magic number column, and you create rows with the following values...

    421
    422
    423
    424
    ...
    936
    937
    938

    now let's say you delete number 423

    do you seriously want to renumber a few hundred rows so that 424 becomes 423, 425 becomes 424, 426 becomes 425...

    what happens to the contents of that other table?

    where you previously had a row like XYZ505, suddenly XYZ505 no longer references the same original row!!

    this is truly a mess and i'm afraid i cannot help you further
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2004
    Posts
    362
    Yes that is it, surely it can be done via a query on the fly? As in my original question with the Dmax function? The autonumber will remain untouched, it is just the other currently empty field in the query eg the expression created in the query which will show the incremented numbers without gaps.

    If you want me to spend a while explaining the ins and outs of absolutely everything I would be here all night.

    It isnt a mess, infact the database is something Im rather proud of and have put a lot of time into, and I really dont appreciate that comment. And you havent helped in any way what so ever anyway.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,273
    I agree with r937 when he (she?) says that it's not a good idea; it's even against several basic principles of a relational database system. However there are times when the financiers of a project cannot be deterred from their clumsy ideas: they refuse to hear you whatever right your arguments can be.

    For finding the next number in a sequence, you can youse the MAX() function in SQL or the DMAX() function in VBA, but I reckon that you already know that.

    What I would do would be to use a display value (face value) that would always remain in sequence and keep this value associated with the true primary key (autonumber, GUID, etc.), that primary key being the only key used to build relations in the database.

    Practically, you can create a table with two columns: Primary_Key (autonumber) and Display_Key (Long integer). When you need to re-arrange the keys (because there is a deleted row leaving a gap in the system, etc.) you could simply run an update query on that table.

    If you actually try to modify the key values used to build the relations in your system you'll end up with a solution that will be slow, unmanagable and, in the end messy. Specially because the lack of triggers in Access will refrain you from implementing a mechanism that could truely guarantee genuine referencial integrity: it will only rely on code... and code might break (and often do).
    Have a nice day!

  13. #13
    Join Date
    Aug 2004
    Posts
    362
    This piece of code simply placed in a query does exactly what I want -
    Code:
    Sequence: DCount("id","tblTest","ID <=" & [ID])
    It lists a sequence.

    Sorted, and in no way was it "exceedingly difficult"

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, whaddya know

    when you went roaring past the DMax suggestion in post #2, i figgered you actually wanted to store this separate no-gap number

    in post #3 you confirmed this idea by saying that you wanted to "insert a starting number in the first empty field (called "CurrentID") of my table"

    this was -- and still is -- a terribly messy and decidely difficult task to pull off successfully

    but nooooo....

    turns out all you really wanted was to slap an ascending incremental number onto the result set produced by a query

    totally different kettle of fish, man

    my apologies for getting faked out by your description of the problem in post #3

    good luck and please try not to be so harsh on people who are trying to help you for free
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Aug 2004
    Posts
    362
    I didnt go "roaring" past anyone, what I did was find the solution to the problem, Im sorry if you mis-understood my question, but you were being rather irritating in the way you were responding to me.

    I dont intend to sound harsh, but I have to respond like-for-like, especially when words like "truely a mess", exceedingly difficult, it is also unnecessary" are used, which quite frankly was a bit insulting and I felt the original question was being mis-interpreted into something else.

    Maybe I could have worded the original question differently.

    No hard feelings though, all is well with the world and I got there in the end

Posting Permissions

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