How to re-create the AutoNumber column with values assigned?

[lookingforK]

Hi,

I am using a form based on a table in MS Access 2010 for data entry.

The Data Type for column ID is AutoNumber with Increment=1. When I found ID for recent entries gets an anomaly (e.g. should be … 3, 4, 5 …, but it shows … 3, 5, 6 … that misses 4), I tried to refresh/change the ID column with the following steps:
1). Change the Data Type for column ID from AutoNumber to Number
2). Add a new column IDD and set its Data Type as AutoNumber
3). Delete the original column ID
4). Change column name IDD into column name ID

However, I got a problem with step 2): here the order # for the records becomes messed up, e.g.
Should be:
ID IDD
1 1
2 2
3 3
5 4
6 5
7 6
… …

But, I got::banghead:
ID IDD
1 3
2 4
3 5
5 1
6 2
7 6
… …

Why? How to deal with this problem?
Is this caused by the relationships between this table and other tables?

Thank you in advance.

 

[pr2-eugin]

AutoNumbers are very tricky.. Although the basis of that would be, if Numbering means something to you, do not use Autonumber type..

Autonumber in Access provides you a guarantee for UNIQUE value, it does not promise if they would be positive, incremental.. It might be even negative, non sequential numbers.. The simple way to reset the counter would be compact & repair, although this is not always 100% to go back to 0 and start with 1..

There are several approaches to simulate the process of Autonumbering, DMax() + 1 being one such method..

 

[David R]

Stop trying to assign Autonumber a purpose. It won't ever be meaningful, it's just there to give you a unique record ID, which is what it is doing.

You can use VBA to step through your records and assign a sequential value to this [IDD] field, but that should NOT be the same as your primary key.

 

[lookingforK]

Thank you both ...

 

[jdraw]

An autonumber is simply a meaningless number that is unique. It is used by the system to have a unique identifier on a record. There is an excellent article on what an autonumber is Not
at http://www.utteraccess.com/wiki/index.php/Autonumbers


As a somewhat trivial analogy (which may be off base), consider the following:

You have 5 bills in your wallet.

2 --- twenties (serial numbers 123243353 and 882772632 )
3 --- fives (serial numbers 435543322, 837664422 and 5006949390)

The serial numbers, which I'm sure are important to the Treasury department or Central bank, are pretty meaningless to you and I. Whether or not they are sequential has no real significance. Do you have any idea what serial numbers are on bills when paying for something or getting change??

As for queries:
How much money do I have?
How many bills do I have?
How many 20s?
How many 5's?
How many 10's?

Queries are the means to deal with the bills. Don't attach any meaning to an autonumber for your or your clients' use.

Good luck with your project.

 

[ButtonMoon]

As a somewhat trivial analogy (which may be off base), consider the following:

You have 5 bills in your wallet.

2 --- twenties (serial numbers 123243353 and 882772632 )
3 --- fives (serial numbers 435543322, 837664422 and 5006949390)

The serial numbers, which I'm sure are important to the Treasury department or Central bank, are pretty meaningless to you and I. Whether or not they are sequential has no real significance. Do you have any idea what serial numbers are on bills when paying for something or getting change??

I think it's a poor analogy because serial numbers would be a natural key for dollar bills and they are as far as I know the only way to identify bills, which is exactly what autonumber columns ought not to be.

Your example money queries are all about counting and aggregation, which misses the point about the purpose of a key which is to identify individual facts recorded as rows in the database.

lookingfork, the point here is that an autonumber is generally a poor choice for a key that is exposed to users and that people rely on to identify information in the database table. Consider choosing a different key if possible. If your users never see it and if you don't write any program logic that depends on the autonumber sequence then you generally won't need to worry about its gaps or other unexpected behaviour. Of course this doesn't exactly solve your problem because you quite probably still want to create that sequential key for users after all - but it's easier to solve that problem if the column in question is not auto-numbered.

 

[jdraw]

Buttonmoon,

I really think you're going way too deep. The OP still wasn't convinced after Paul and David's comments. I was just showing that a meaningless number could serve a system purpose, but was of no significance to him.

If you have a better, simpler analogy, then please post it. I just came up with that while responding - really to show that sequential numbers weren't important to his situation.

Yes natural keys are great, where appropriate. I've seen applications that used ProvinceCode (NF -a seemingly natural key), and PostalSortation, only to see a Province change (Newfoundland to Newfoundland-Labrador [NL]) and Quebec change from PQ to QC, and then there was the partial breakup of NorthWest Territories to Nunavut.

Totally agree that an autonumber should not be exposed to users. I try to say that autonumber is for system use ONLY, not for human consumption.