primary key autonumbers problem

[jinz]

My autonumbers are not in order for tables.



As in for example

for a customer


customerid customer first name customer last name

17 john parker
18 james johnson
19 alex scott


this is right at the top of the table the first sets of data,

i want it to be

customerid
1
2
3
4
5
,etc

Why does it have a random number like "17" when I DO NOT have the first 17 customers !

[JoeM]

AutoNumber does not automatically reseed itself back to 1. So if you import 16 records (originally usually assigned 1-16), but then delete them and re-import, it will start up at 17.
Likewise, if you delete random records here and there, you will have "gaps" in your Autonumber. It shouldn't really matter though. The only thing Autonumber should really be used for is to guarantee a unique number (you should NOT try to get the record count for a table by looking at the largest value of Autonumber!). You can use DCOUNT or an Aggregate Query to count your table records.

Note, if you delete all the records from a table, you can "reset" Autonumber to 1 by running a "Compact and Repair" on your database.
If you have existing data that you do not want to delete, but want to reset the Autonumber to start at 1, one way is to delete the Autonumber field, then add a new Autonumber field to your table.

[ssanfu]

You do not want an AutoNumber in this case. The AutoNumber field is not designed as a user-visible field. The main problems are:

* You are not going to be able to guarantee the number remains sequential or even positive.
* You can not change the number.
* You can not "re-start" or skip numbers

The point is an AutoNumber is intended to be an AutoNumber, not a SequentialNumber. It's intended to provide a system-visible unique record identifier, not a user-visible one. It's so close it really makes you want to use an AutoNumber, but it's not close enough.


The AutoNumber Data Type
---------------------------
The AutoNumber data type stores an integer that Microsoft Access increments (adds to) automatically as you add new records. You can use the AutoNumber data type as a unique record identification for tables having no other unique value. But the uses should never see the number.


Read: http://www.applecore99.com/tbl/tbl012.asp



PS: Also see:http://access.mvps.org/access/tencommandments.htm Shouldn't use spaces in names

[JoeM]

PS: Also see:http://access.mvps.org/access/tencommandments.htm Shouldn't use spaces in names

Great link! I bookmarked it. I am always telling people not to use spaces in their names - now I have backup. Lots of other good tidbits to pass along to newbies.

[ssanfu]

Also note the link to the use of "Lookup Fields"

The Access Web is one of most used reference sites>>>> http://access.mvps.org/access/

[JoeM]

Also note the link to the use of "Lookup Fields"

Yeah, I probably have been guilty of that one from time-to-time. I do use them on occassion, but pretty sparingly.

I used to frequent that site years ago, but not as much recently. I agree, they do have a lot of good stuff on it.

[orange]

Here's another slant on autonumbers. Very worthwhile reference.

http://www.utteraccess.com/wiki/index.php/Autonumbers