Autonumber

[Bhenry]

I have an autonumber sequencing on a table to keep the table in the same
order after running a query and export the results. One of the numbers from
the table fell off and the sequencing skipped, can anybody tell me why this
would happen? E.g. the sequencing was 9321, 9322, 9324, 9325.

 

[Golfinray]

Autonumbers are not meant to be sequential and if you design around them
being sequential, sometimes they change and you now have a mess. You could
re-autonumber the table and see what happens.

 

[Klatuu]

If it is truely an Autonumber field in your table, you can't expect it to
have no gaps. In fact, it will almost surely have gaps. If a record is
deleted, a gap is created. If a user begins a new record, but cancels
before saving the record there is a gap. Autonumbers are not reused.

In addition, what you are doing is an inappropriate use of an autonumber.
They should really only be used as surrogate primary keys.

Also, you cannot expect a table to be in any order. A table is just a
bucket of stuff. If you want to export it in a specific order, you should
use a query with an OrderBy clause to do the export.

 

[Steve Schapel]

Bhenry,

I think the most common reason for this is if the entry of a new record
is aborted.

The general advice is to not use an Autonumber if you care what the
value of the field is.

 

[Bhenry]

Thank you all for your responses. I'm new at this, can you help me with this
please then. I have an excel spreadsheet with a column of numbers in a
particular order that I need to pull into an access query to then put a
letter on the number once it is valid or leave blank if not valid, then
export the query in the same order the numbers came in. How do I go about
doing this without using the autonumber?

 

[Golfinray]

Use file/get external data/import. Then when you get the file imported you
can add a letter to each number. When you export it (right click on the file
and select export) it should go as is.

 

[Bhenry]

I've tried that but when I export it changes the order in which the numbers
were imported in. That's why I imported it into a table with the autonumber
so what when I exported the query with the autonumber field it will stay in
that order until this problem.

 

[Klatuu]

Tables have no natural order. Rather than export the table, create a query
based on the table sorted how you need it and export the data using the
query instead of the table directly.