Why won't Access re-number the ID when I delete records?

[Beaverkettle]

The lefthand-most column is for numbers, just so we know how many records (of
names) we have. I recently had to go through the entire database of almost
2,000 names and delete some duplicates. To my surprise, the numbers didn't
"re-number" so that now we're simply MISSING some numbers. Meaning: the
sequence does something like 1,2,3,5,6,8,10, etc. So the numbers 4,7 and 9
are MISSING. Help!

 

[golfinray]

Autonumbers are not meant to be sequential, just to be unique. They are
primarily meant for a key, not for tracking records.

 

[Armen Stein]

Autonumbers are not meant to be sequential, just to be unique. They are
primarily meant for a key, not for tracking records.

You can use a Totals query (with Count for the Total type) to count
the number of records in your table.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tedmi]

To add to Multon's response, there is no concept of "record number" in a
relational database. What is "Record 1" depends on the order you specify for
diplaying the records - e.g the "first record" may be different ifi you
display records ordered by name, vs. ordering by zipcode. The concept of
numbering records applies only in a report.
To achive this, insert a text box as the first field of your detail section.
On the Data porerties diaoliog for this textbox, enter =1 for data source,
Overall for running sum.
-TedMi

 

[BruceM]

Autonumber or not, they will not renumber unless there is code to perform
the task. To the OP, it seems you are looking at the data in the left-hand
column as if it was a spreadsheet row, where if you delete a row the next
row down will take over the row number from the deleted row. Access does
not work the same way.

If the idea is to obtain a count of the number of names, you have several
options. You should not be entering data directly in the tables, but if you
open the table you will see a record count at the bottom. If you base a
form on the table, likewise the built-in navigation buttons will give you a
record count. You can make your own record counter, if you like.

If you want a numbered list of names, the simplest is to use a report.
Place a text box on the report, with its Control Source set to =1 and its
Running Sum property set to Over All.

For forms and queries there are some options here:
http://www.lebans.com/rownumber.htm