Auto number fields

[Stephen Brown]

I have a fairly simple database that acts as an annual
seal register.

It uses an autonumber record to generate the seal number.

In creating the new database for 2004, I have copied the
2003 database and deleted all the records. I have then
deleted in field "SealNo03" and created a new
field "SealNo04" as an autonumber field. However, when
creating the first record in the seal 2004 database
instead of starting from 1 it starts from the next number
that was due for the old field.

Is there a simple way around this, without having to
create the whole database from scratch?


Thanks



Stephen Brown

 

[Roger Carlson]

You have opened a number of cans of worms. The simple answer is to compact
the database after you delete all of the previous year's data. This will
reset the autonumber back to 1.

However, I do not suggest (and most experienced developers agree) using an
autonumber for an information bearing field. You can easily lose a number
from your sequence. The actual value of an autonumber field should be
irrelevant. You should create your own sequential number separate from the
autonumber. On my website (see sig below) is a small sample database called
"AutonumberProblem.mdb" which illustrates one way to do this.

Secondly, I do not advise creating a separate database for each year. What
happens when you want to create a query that spans years? You should be
keeping all the data in a single database and use a date field to separate
the years. You can easily query for all the data from a single year and you
can also query across years.

HTH