If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > How to auto-increment a field by number?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
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?
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
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.
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
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.
Reply With Quote
  #8 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
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...
Reply With Quote
  #10 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
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.
Reply With Quote
  #12 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,120
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!
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
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"
Reply With Quote
  #14 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On