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

  • Thread starter Thread starter Beaverkettle
  • Start date Start date
B

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!
 
Autonumbers are not meant to be sequential, just to be unique. They are
primarily meant for a key, not for tracking records.
 
Autonumbers are not meant to be sequential, just to be unique. They are
primarily meant for a key, not for tracking records.
Click to expand...

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
 
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
 
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
 
Back
Top