Access 2002 ID making Numbers Consecutive in ID Field

  • Thread starter Thread starter myland
  • Start date Start date
M

myland

Hello,
In Access 2002 ID block
Does anyone know how to make the numbers go consecutively
like 1, 2, 3 and so on. Now my numbers run in order but
if I delete a report then it leaves a gap. For an example
1, 2, 3, 4 I delete 2 or even 3. How do I make 4 report
to become a number 2 under the ID. I have at lease 130
reports in Access. Therefore, I have plenty of gaps.
This would really help me.
Thanks,
Tom
 
In a relational database the numbers are not supposed to stay consecutive if
you delete one. What happens to numbered invoices when you drop one
accidently in the trash? or spill coffee on it? You lose the number, right?
The same with virtual paper. In most cases you never need to see the number
anyway. It's just used to connect data.

If you change the numbers which have data connected, all the connections
will be wrong and you'll have completely useless data.

If you really want to do it, you can delete, then re-create the autonumber
field. I can give you a 100% guarantee that your database will be screwed
up. Please don't come back and ask how to fix it.

Merry Christmas.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hello,
In Access 2002 ID block
Does anyone know how to make the numbers go consecutively
like 1, 2, 3 and so on. Now my numbers run in order but
if I delete a report then it leaves a gap. For an example
1, 2, 3, 4 I delete 2 or even 3. How do I make 4 report
to become a number 2 under the ID. I have at lease 130
reports in Access. Therefore, I have plenty of gaps.
This would really help me.
Thanks,
Tom
Click to expand...

It sounds like your "reports" are not Access Report objects, but are
records in a Table with an autonumber field. An Autonumber has one
purpose, and one purpose ONLY: to serve as a guaranteed-unique key for
a record. Autonumbers will *always* have gaps; not only will deleting
a record produce a gap, as you have seen, but even starting to enter a
record and hitting <Esc> to cancel it will do so.

Normally one would not want to arbitrarily renumber records in a
table. Suppose someone has printed out Report 118 and passed copies
around, and there are a whole lot of sheets of paper (and a whole lot
of human memories) using "118" as a shorthand for that report. Would
you want that report to suddenly become 115? Will you go around with
White-Out and change all the papers? And what about the memories? <g>

Basically, Autonumbers should NOT be used if gaps are not tolerable.
If you could explain what your "reports" are, and why they must be
consecutive (and why it's OK to renumber 127 reports on deleting two)
someone can probably help you come up with a different technique to
number them.

John W. Vinson[MVP]
 
Back
Top