Reset Autonumber?

[Gary J. Dikkema]

I build tables and would like to use the autonumber feature for a field to
keep data in order... however, seems like every time the file would be
emptied and appended (reloaded) the autonumber would be incremented from
last usage.

So if you put 1200 records in the table and then empty and append new data,
the first record would be 1,201 and the rest higher than that.

Short of creating a number field and controlling the number for each record,
is there a simpler way to ensure that the autonumber gets reset to 1 always?

TIA.

 

[Jason Simmons]

delete the column, close and re-open the database.
insert a new column (give it same name) set its type to autonumber.

it will re-number all rows starting at 1

 

[Granny Spitz via AccessMonster.com]

I build tables and would like to use the autonumber feature for a field to
keep data in order... however, seems like every time the file would be
emptied and appended (reloaded) the autonumber would be incremented from
last usage.

When the table is empty, compact the database. The AutoNumber will be reset
to 1 if you're using Access 2000 or newer.

 

[Jeff Boyce]

If the only purposes you have for your autonumber (?key) field is to "keep
data in order" and to uniquely identify rows, why do you care where it
starts?

Moreover, if your autonumbered table is a "parent" in a one-to-many
relationship with another table, that table's foreign keys would be orphaned
if you re-set autonumbers in the "parent" ... or WORSE! You could end up
with a "child" record for ParentID = 10, but it wouldn't be the same parent
after resetting!

Access autonumbers are designed to be unique row identifiers, and are
generally unfit for human consumption. If you are using them for any other
purpose (including "keep data in order"), you can't be sure they will
perform these additional duties.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

If the intention is to show records in the order they were entered into the
database, would the autonumber work for that? I realize that a table's data
is not in any particular order, but how about a query sorted by the
autonumber field, or a report ordered by the autonumber field? Would the
records be in the same order as they would if there was a date/time stamp
(e.g. a field with Now() as the default value)?
I don't use autonumber for sorting, but I am curious as to why it shouldn't
be used to show the sequence of records in a simple database. I believe
that with replicated databases and in some other situations the autonumber
does not necessarily behave in as orderly a manner as would be needed to
assure chronological order in which the records were entered. Do I
understand correctly? I ask just because I am trying to increase my
understanding.

 

[Jeff Boyce]

Bruce

You've described one of the situations in which autonumbers are not
sequential. Another is when you set the autonumber to "random".

And if you're looking for a guaranteed-no-gap sequential numbering system,
autonumbers do NOT provide this. If you were designing a system that would
need to have the records audited, the auditors get fussy when there are
"missing" numbers in the sequence. In Access, this could happen if a record
had been deleted (auditors, please look the other way), but it can also
happen if you start to enter a new record, then change your mind. The
autonumber that was being assigned is forevermore skipped (unless you mess
with resetting!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

Thanks for the reply. I use a DMax system for no-gap sequential numbering.
My question had more to do with the chronological order than with actually
using the number for anything. You have confirmed that there are situations
in which the "order" can get messed up, which was really the main drift of
my question. I work with a database in which some records were entered
several years ago, and others were entered more recently. The autonumber
gives me some sense of how long the record has been in the system, although
the information is meaningless since it doesn't matter when the record was
entered. It's just something I noted in passing and started wondering
about. Thanks again for taking the time to comment.

 

[Gary J. Dikkema]

All good questions and answers.

In my case I subset my database based on criteria; the subsetted file needs
a simple to use key for later calling up each record based upon external
criteria... I could use telephone but am trying to keep the key strokes to 5
or less per session... that's where the autonumber could be used.
Alternatively I could create a number field and as I write each record to
the subsetted file increment it by ONE.

The information in this subsetted file would then be used to update the main
table. Updated records on this file would be deleted... or perhaps the file
would be rebuilt... depending.

I can't auto compress as the file is locked down or secured and the user
access doesn't allow compress to run. Besides, if they rebuilt the same
day - before a compress had been run - the number would still be too large.

Perhaps a number field that I can reset and then increment would work best.

 

[Jeff Boyce]

Bruce

I'll go one step further (could be 'over the edge').

WARNING! Doing this could compromise the integrity of your data if you have
any "child" tables that depend on the "parent" table for a foreign key. In
the venacular, you could hose your data...

DOUBLE WARNING! Don't try this at home. Think twice before trying it at
work...

Create a simple autonumbered table. Add 5 records. Delete record w/
autonumber = 3.

Check the table -- no 3, right?

Create an append query to append to the table. Append a value of 3 to the
autonumber field, plus whatever else you want in the record.

Re-check the table. If I recall correctly, you'll now have an "autonumber"
of 3!

(You could have used any value, but if it matched an existing autonumber,
the append would fail.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

I can confirm that it works as you said, but I must admit I can't imagine
why I would ever do something like that. That being said, the fact that it
is possible in some odd scenario I can't anticipate just now to mess up an
autonumber is abundantly clear. The main thing is that if it is possible to
screw it up, some day it will happen, even if I can't see that from here.

 

[Joseph Meehan]

I build tables and would like to use the autonumber feature for a
field to keep data in order... however, seems like every time the
file would be emptied and appended (reloaded) the autonumber would be
incremented from last usage.

So if you put 1200 records in the table and then empty and append new
data, the first record would be 1,201 and the rest higher than that.

Short of creating a number field and controlling the number for each
record, is there a simpler way to ensure that the autonumber gets
reset to 1 always?
TIA.

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.



See:
http://www.lebans.com/rownumber.htm

 

[Gary J. Dikkema]

Poor example Jeff...

Autonumber gets assigned by the system; NEVER the user.

Thus 3 will NEVER get re-assigned.

 

[Gary J. Dikkema]

Thanks!

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.



See:
http://www.lebans.com/rownumber.htm

 

[Jamie Collins]

I use a DMax system for no-gap sequential numbering.

Remember that anyone with permissions can insert explicit values into a
column, including an autonumber provided they are unique. When testing
a table, get into the habit of inserting the values 2,147,483,648 and
2,147,483,647 to test whether the application can handle explicit
values in the sequential/autonumber column.

Jamie.

--

 

[Jamie Collins]

Poor example Jeff...

Autonumber gets assigned by the system; NEVER the user.

Poor interjection, Gary <g>.

Autonumber can be assigned by a user e.g.

CREATE TABLE Test3 (
seq INTEGER IDENTITY(1, 1) NOT NULL UNIQUE,
data_ool INTEGER NOT NULL)
;
INSERT INTO Test3 (data_ool) VALUES (1)
;
INSERT INTO Test3 (seq, data_ool) VALUES (2147483647, 2)
;
INSERT INTO Test3 (data_ool) VALUES (3)
;
SELECT seq, data_ool
FROM Test3
;

Jamie.

--

 

[BruceM]

Rather to my surprise I discovered that it does happen as Jeff described.
If there is a gap in the autonumber sequence, and you use an append query
with that number as the value to be appended to the autonumber field, it
will attempt to insert that value into each record, and will fail in all
instances but one. Again, it's hard to imagine how that could happen, but
if it can I expect it eventually will in some instance.

 

[Jamie Collins]

Rather to my surprise I discovered that it does happen as Jeff described.
If there is a gap in the autonumber sequence, and you use an append query
with that number as the value to be appended to the autonumber field, it
will attempt to insert that value into each record, and will fail in all
instances but one.

There is another interpretation: if there is a gap in the autonumber
sequence, and you use an append query with that number as the value to
be appended to the autonumber field, it will successfully insert a new
record with that value and all other rows will remain unaffected.

My only surprise is that you were surprised <g>.

Jamie.

--

 

[Jeff Boyce]

Gary

Can't, ... or shouldn't?

There may be differences based on version, but it worked on this PC before
posting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Gary J. Dikkema]

You're right.

Alright for lack of better term... it's a shoddy way to use it.

<VBG>

That's my term and I'm sticking to it. <VBG>

 

[Gary J. Dikkema]

SHOULDN"T sounds like a good term...

<VBG>

Gary

Can't, ... or shouldn't?

There may be differences based on version, but it worked on this PC before
posting...

Regards

Jeff Boyce
Microsoft Office/Access MVP