Autonumber 2

[jeffcravener]

I have a table that I use autonumber on, I did a query append to get some former records into the new table, the last one being 308. Then when i added a new one, it numbered it 1248....how do i get it to number the next value...which would be 309?

Thanks!

 

[lespaul]

that's the way autonumber works. if you add 500 records to a table and then delete them, the next number will be 501.

Have you assigned some "meaning" to the autonumber? If so, you should rethink your use of this feature. It should be a unique identifier with no meaning.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jeffcravener]

Well, its an issue log, so the autonumber would be the Issue number of that particular issue.

 

[lespaul]

So, you're saying that 'Issue Number' (which is the autonumber field) has meaning - therefore you shouldn't be using autonumber. Look in the Access FAQ areas for information on creating your own autonumber that will follow the rules you need set.

 

[jjlogan]

Sounds like Issue Number really does not have special meaning in itself. Isn't the Issue Number just a unique identifier for an issue? If so, I think you could use Autonumber as is, unless you don't want the issue numbers to start in 500's because it would seem like there are too many issues?
Jeff

 

[lespaul]

The original post indicates that there is a problem because it went from 308 to 1248. If Jeff wants it to go "in order": 308, 309, 310 - then it has some kind of meaning and an autonumber should NOT be used.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jjlogan]

It seems to me that Leslie interpreted that issue number has meaning per her statement: "So, you're saying that 'Issue Number' (which is the autonumber field) has meaning". Actually, requestor just said "it is for Issue Numbers." I think the requestor needs to make the final judgement.
Jeff

 

[jeffcravener]

haha...well...it depends. I see what she is saying as far as it having meaning. But I don't get why an autonumber can't have "meaning".

I basically want the Issue Number to increment based on the prior record. So that it is quick and easy.

So does it have meaning, technically yes, in case we ever give that number out, its a quick lookup.

You guys are the experts, so any suggestions on the "right way" are obviously appreciated!

Thanks everyone!

 

[Golom]

I think that what Leslie meant is that there is an expectation that each new number should be one more than the highest number currently in the field. Such an expectation implies that the numeric value of the number has meaning beyond mere uniqueness. (e.g. counting the number of Issues in the table.)

The fact that it was not returning that result is, I gather, why jeffcravener asked the original question.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jeffcravener]

Exactly...so, is there no way to get the autonumber back on track? It has to be keeping the value somewhere in order to increment at all....i just don't get why it decided to start at 1250 something!

 

[jjlogan]

Thanks for feedback Jeff.
First I agree with you that Issue Number would be used as a lookup number to get back to the specific Issue description. In this usage, the autonumber does provide a unique id number - there is only one autonumber for each issue (no duplicates).
But, when we start talking about "meaning" I think of an ID number that provides clue as to the Issue. For example, you could create issue numbers that are alphnumeric with (say) the first character(s) refering to a class or category of issues, and the last part of the unique ID being numeric (1 to infinitity) depending on how many issues exist within a category.
So, if you just need a unique ID, then autonumber is fine. If you want to start the autonumber at 1 for some reason other than having just a unique number (e.g. want the ID number to at least not misresent how many issues there are), then option 2 is re-start the numbers by appending to a new table. Option 3 is to create an ID number that has embedded meaning it it as example above.
Jeff L

 

[Golom]

If you don't use the autonumber elsewhere in your system (for example, as a foreign key into this table) then you can delete the autonumber field, save the table, and then recreate it. That will assign new autonumbers beginning with 1 and incrementing by 1. It may not however, assign the same number to a given issue that it had before.

That will work but it's subject to the same problems. If you delete numbers from the end of the table then once again, you will have a gap in the numbers. As Leslie said, that's how autonumbers work. If you really need sequential numbers then you will need to generate your own numbers rather than relying on autonumber.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jjlogan]

Another option is to never delete records physically - if you have to delete an issue (or make the issue not applicable anymore), use a Field called Deleted (or similar) and set it to Yes. You then maintain the history of all issues and potential issues that got settled or determined not to be an issue.
Just a thought.
Jeff

 

[jeffcravener]

OK, that sounds like a quick solution, deleting the field then putting it back in. I'll give that a try. And the extra field to check if a record is deleted is a good idea too! Thanks everyone!

 

[lespaul]

You don't have to delete the record to get missing/skipped numbers. If you start to create a new record and then discard, you have used an autonumber and there will be a missing number.

 

[Lilliabeth]

I don't get why an autonumber can't have "meaning".

Because if it has meaning, some one will start caring what the values are (like is happening in this thread) and you could spend the rest of your career chasing that rabbit. Want a sequential number that has meaning? Cool. That's sure easy enough to do, so why do it with an autonumber data type where you give up all control?

If you care about the value of the autonumber, you are not using it correctly. It's a handy way to relate tables to each other, and for that, it is it's meaninglessness that is attractive - if it had meaning, some one would decide they want to change it and since it is the field that is common to multiple tables, changing values is pretty scary in a mature database.

See the 7th commandment:

http://www.mvps.org/access/tencommandments.htm

 

[Remou]

This is so true its frightening.

.. it is it's meaninglessness that is attractive - if it had meaning, some one would decide they want to change it ..

 

[Flight15]

Want a sequential number that has meaning? Cool. That's sure easy enough to do


care to explain?

 

[lespaul]

sure, take an invoice number.

Most people want sequential invoice numbers in a system. An invoice number has meaning.

Don't use an autonumber in this situation, create your own process to find the last used invoice number and increment.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jeffcravener]

Thanks everyone! I will see about creating a field that is number based but not sueing the autonumber feature!