2

I'm using MS Access and have created a simple table. I have one column as the standard ID primary key (renamed to Number). I deleted a selection of rows, but now when I go to the next row, the Number column counts from the deleted numbers.

E.g. it looks like:

Number Name
1       etc
2       etc
3       etc
6       etc
7       etc
8       etc

Where rows 4 & 5 have been deleted.

I removed all the rows that came after the problem (i.e. 6,7,8 in this case) but then it starts from 9. Is there any way I can start the count back at 4 (as I have rows 1,2,3 left)?

dgBP
  • 1,681
  • 6
  • 27
  • 42

2 Answers2

3

That is what an autonumber is supposed to so. If you need a counter that means something, you should not use an autonumber.

Access is a relational database, if you could delete a row and then add a new row with the same number, you would throw the relationships out of kilter.

If you need a sequential number see Access VBA: Find max number in column and add 1

If this is a once-off problem, you can delete the current autonumber field from the table and save, then add the autonumber again, but it would be much better to forget about a sequential autonumber. Autonumber should never be shown to the user. It can never be relied upon to be anything but unique, and if you mess about enough, not even that.

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • ok, thanks. As I'm entering a fixed list of entries with unique sequential numbers associated to the entries, I thought the ID field would be suitable enough. Then I made a mistake and deleted the rows... oh well, I will do as you said as [hopefully] a once-off problem. – dgBP Mar 16 '13 at 20:53
0

The real problem is that Access is pretty stupid. E.g. if a new table is created and data is entered for the first time but the row is incomplete, the number will still skip when you go back to the table and reenter the data. Even if it was a blank database and there would be no conflicts/error in relationships by doing so. This is particularly frustrating as an instructor because some other behaviours of Access make this very likely to happen.

NumNums
  • 1