sglee
asked on
Starting AutoNumber field with 1,000 in MS ACCESS
Hi,
I have a database system where I have ORDER table (and order item table and others). The order table has a field called "Order Number".
The data type of Order Number field is "AutoNumber", so naturally it begins with 1 in a brand new table.
However the user wants that number to start from 1,000 instead. (Apparently when you give out the order number 1, it does not leave a good impression as opposed to 1,000).
I thought about creating 999 empty records (so that each record has a number 1 thru 999 in order number field) and delete them. That way when the user clicks [CREATE NEW ORDER] button in Order Entry Form, the system will populate 1,000 in the order number field.
Is there a better way of accomplishing this? Any possible issues that I am not aware of?
I have a database system where I have ORDER table (and order item table and others). The order table has a field called "Order Number".
The data type of Order Number field is "AutoNumber", so naturally it begins with 1 in a brand new table.
However the user wants that number to start from 1,000 instead. (Apparently when you give out the order number 1, it does not leave a good impression as opposed to 1,000).
I thought about creating 999 empty records (so that each record has a number 1 thru 999 in order number field) and delete them. That way when the user clicks [CREATE NEW ORDER] button in Order Entry Form, the system will populate 1,000 in the order number field.
Is there a better way of accomplishing this? Any possible issues that I am not aware of?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
glad to see you answering questions, Bing ~
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
However the user wants that number to start from 1,000 instead.You need to redesign this.
The AutoNumber is for you and the database and should never have any meaning.
Order numbers are for humans and must created separately.
Even if you fix the start number, you'll get in trouble when the user deletes an order and the AutoNumber gets lost. And numbers may change if the database should need a repair someday.
/gustav
to correct a common misconception that "Autonumbers are unique". In theory that is true. In reality, a table in only assured to have unique autonumbers if the autonumber field is a Primary Key or has a Unique Index.
I can understand fixing the start number to keep the number of digits more consistent.
... besides, incorporating ideas from others helps them get more involved in the project. In the end, what is more important? Where an autonumber starts or seeing a smile on someone who feels they made a great contribution?
`
I can understand fixing the start number to keep the number of digits more consistent.
... besides, incorporating ideas from others helps them get more involved in the project. In the end, what is more important? Where an autonumber starts or seeing a smile on someone who feels they made a great contribution?
`
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
@crystal
thanks for your greeting. nice to meet you online, a MVP for 10+ years!
i believe we must had passed each other by somewhere at MVP summits in Redmond. :)
thanks for your greeting. nice to meet you online, a MVP for 10+ years!
i believe we must had passed each other by somewhere at MVP summits in Redmond. :)
ASKER
@BING
"better know what's the background or specific reason that makes the user have such a concern. e.g. do they need to do some kind of calculation on the AutoNumber field in the near future?" ---> I did not ask the reason why and they don't have any prior order system to convert from, so I don't think it is because of calculation reasons.
I asked the user to re-consider the request and a moment ago, he said it was fine to start with 1.
But I will try the suggestions made above and post the results.
"better know what's the background or specific reason that makes the user have such a concern. e.g. do they need to do some kind of calculation on the AutoNumber field in the near future?" ---> I did not ask the reason why and they don't have any prior order system to convert from, so I don't think it is because of calculation reasons.
I asked the user to re-consider the request and a moment ago, he said it was fine to start with 1.
But I will try the suggestions made above and post the results.
@Nick, many of us who know have been bit or seen someone who has ... glad you won't have to go through the hard knocks ~ when I get a database from someone else, unique index on autonumbers is something I check for.
@Bing you're welcome and thanks ~ perhaps we have ... what is your name?
@Bing you're welcome and thanks ~ perhaps we have ... what is your name?
@sglee
I think you missed the point.
It is irrelevant what the starting number is for an autonumber
because the autonumber is for you the developer and the database alone
Making the sequence, value, and completeness of an autonumber field important to a user is going to be a fail-whale.
Create an order number system that meets the users needs.
Don't use an autonumber as its basis
I think you missed the point.
It is irrelevant what the starting number is for an autonumber
because the autonumber is for you the developer and the database alone
Making the sequence, value, and completeness of an autonumber field important to a user is going to be a fail-whale.
Create an order number system that meets the users needs.
Don't use an autonumber as its basis
even though autonumber values are irelevant as long as they are unique, the fact that someone needs to compare them to make sure data is going into the right places remains. That is easier to do when the number of digits is the same instead of, for instance, 17, 434 and 1198. It is also easier for customers if the autonumber is given significance. Many of the people I help with Access DO use autonumbers to mean something important sometimes too -- and that is fine, in my opinion.
Numbers skip, of course -- that is ok. Perhaps the issue is more where to start and how many digits to have.
Numbers skip, of course -- that is ok. Perhaps the issue is more where to start and how many digits to have.
There are those topics that generate strong debate.
This is one.
Many folks feels strongly that an autonumber makes an excellent PK, that PKs should be numeric and have no significance to the user.
I expose the PK to the user, but do not label it.
The user knows that it should trend upward over time, that's it.
the fact that someone needs to compare them to make sure data is going into the right places remains.
I have no idea what that means.
Autonumber PKs and referential integrity mean that this isn't an issue in anyway that I can conceive.
It is also easier for customers if the autonumber is given significance.
There'll be strong disagreement about that.
Something that looks like a sequence should be continuous, and autonumbers are not.
That confuses users.
Many of the people I help with Access DO use autonumbers to mean something important sometimes too -- and that is fine, in my opinion.
It's a free world. You get to do what you want.
But user significance for autonumbers usually leads to heartache and confusion.
YMMV -- and clearly does.
This is one.
Many folks feels strongly that an autonumber makes an excellent PK, that PKs should be numeric and have no significance to the user.
I expose the PK to the user, but do not label it.
The user knows that it should trend upward over time, that's it.
the fact that someone needs to compare them to make sure data is going into the right places remains.
I have no idea what that means.
Autonumber PKs and referential integrity mean that this isn't an issue in anyway that I can conceive.
It is also easier for customers if the autonumber is given significance.
There'll be strong disagreement about that.
Something that looks like a sequence should be continuous, and autonumbers are not.
That confuses users.
Many of the people I help with Access DO use autonumbers to mean something important sometimes too -- and that is fine, in my opinion.
It's a free world. You get to do what you want.
But user significance for autonumbers usually leads to heartache and confusion.
YMMV -- and clearly does.
But user significance for autonumbers usually leads to heartache and confusion.Right. And the heartache is not the user's but the developer's. Been there, you never go there again.
/gustav
all we can do is present our opinions on the use of autonumbers and let each decide for themselves. Like naming, there is no right or wrong convention as long as there is something consistent ~
If absolute sequence is not needed, I see nothing wrong with using a number that is already unique instead of creating another one ... the engineer in me thinks it is more efficient to use what is there already ~
to each their own ... we all have our own ways ~
If absolute sequence is not needed, I see nothing wrong with using a number that is already unique instead of creating another one ... the engineer in me thinks it is more efficient to use what is there already ~
to each their own ... we all have our own ways ~
ASKER
Is there some type of ACCESS VB code out there that generates a new order number (one greater that previous)?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
@crystal & @Nick
Thanks for the sample code and I appreciate it.
Thanks for the sample code and I appreciate it.