Erratic Auto Number

[Guest]

I am using MS ACCESS 2003

I am importing data from several spreadsheets into a table that has Product
ID as auto number field. The first spread sheet has 16 records and the
Product ID incremented 1-16. The second sheet has 87 records and the Product
ID incremented 17-103. The third sheet has 5 records and Product ID started
at 1456. What might have caused this and how can I keep the Product ID
incrementing consecutively?

Thanks

 

[Guest]

Read thought previous posts, this question comes up everyday.
This answer comes from Rick Brandt:

"If you care about the value in ANY way other than uniqueness, then DON'T
use an
AutoNumber. Their only purpose is to provide a unique value for creating
relationships between tables and/or as surrogate Primary Keys.

They WILL develop gaps (sometimes big ones) and they can even go negative.
They
cannot be reliably used as a record "counter"."

 

[Joseph Meehan]

I am using MS ACCESS 2003

I am importing data from several spreadsheets into a table that has
Product ID as auto number field. The first spread sheet has 16
records and the Product ID incremented 1-16. The second sheet has 87
records and the Product ID incremented 17-103. The third sheet has 5
records and Product ID started at 1456. What might have caused this
and how can I keep the Product ID incrementing consecutively?

Thanks

Any number of things can cause that. Keep in mind that autonumbers are
only designed to provide unique numbers not consecutive numbers. In general
you don't use autonumbers anywhere that someone may see them.

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.

 

[Guest]

Hi.

What might have caused this

The Autonumber field's Seed Property has been reset. This can be caused by
creating a new record with a value of 1455 in the Autonumber field, then
deleting that record, or by having a computer with Jet 4.0 pre-SP-7 create a
new record for this table. This last scenario is eliminated in Access 2003,
because it comes with Jet 4.0 SP-8 so it doesn't have the problem.

how can I keep the Product ID
incrementing consecutively?

The Autonumber is not guaranteed to be sequential, so if you need a field
value incremented by a value of exactly one for each new record created --
without any gaps -- then you shouldn't be using an Autonumber for the field's
data type, because one shouldn't assign an Autonumber to a field that has
meaning. If you need this field to mean something, then create a
user-defined method to assign and maintain the numbers for each record.

You can fill gaps in an Autonumber field by using an append query that adds
new records to the table, but specifies the values of these skipped numbers
to use in the Autonumber field in the SQL statement. This is probably more
trouble than it's worth. Allen Browne has a VBA function that may be used as
as starting point (the current seed is obviously higher than the current
MAX(ProductID), so you would need to change the VBA code to suit your needs)
listed on the following Web page:

http://allenbrowne.com/ser-40.html

Here are examples of several methods of obtaining consecutive numbers in
your ProductID field without using an Autonumber data type:

http://groups.google.co.uk/groups?hl=en&lr=&th=d5fa758ce77511f&rnum=14

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.