Autonumber after inserting data already *newbie*

[fuecheefang]

I need help with starting an auto number feature in the table on Project_Id. I initially started the ID with text format so I can enter the specific id's into the table. Now, that Ive entered the 80+ fields, how can I start an auto number in the ID same table.

thanks. Sorry I am really new to access.

[ranman256]

yes, just add a new field [ProjID] as AUTONUMBER
save.
(you can update any sub tables from the OLD id with the new id)

[JoeM]

You can add an Autonumber field to an existing table.
Simply go to Design View, add the new field, selecting the Autonumber option. It will automatically number the data you have in there.

[fuecheefang]

Hello JoeM, I tried that as well but it changes the existing numbers Ive manually typed in previously

[JoeM]

Hello JoeM, I tried that as well but it changes the existing numbers Ive manually typed in previously

That is correct. You have no control of the Autonumber field; you do not have the ability to manually set any values in the Autonumber field. You cannot keep what you have in that field, and convert it to an Autonumber field going forward.

All the Autonumber field should be used for is to create a unique identifier. If it needs to be something more than that, then you should be using something other than Autonumber - maybe come up with your own identifier. People have created VBA code that do that sort of thing, based on certain criteria.

That being said, it is generally a good idea to have an Autonumber or some other unique identifier in every table in your database.

[June7]

Has your original ID field been used to populate foreign key field of a related table? If it has, then you need to decide whether to retain the original data as PK/FK or to switch the autonumber to PK/FK. Switching will involve updating the related records with the new autonumber values. Whether or not you continue to generate the original custom unique identifier if the autonumber becomes the PK/FK is another decision.

[fuecheefang]

Has your original ID field been used to populate foreign key field of a related table? If it has, then you need to decide whether to retain the original data as PK/FK or to switch the autonumber to PK/FK. Switching will involve updating the related records with the new autonumber values. Whether or not you continue to generate the original custom unique identifier if the autonumber becomes the PK/FK is another decision.

June7 no my Projects table does not have PK, nor does it relate to any other records in other tables.

[fuecheefang]

That is correct. You have no control of the Autonumber field; you do not have the ability to manually set any values in the Autonumber field. You cannot keep what you have in that field, and convert it to an Autonumber field going forward.

All the Autonumber field should be used for is to create a unique identifier. If it needs to be something more than that, then you should be using something other than Autonumber - maybe come up with your own identifier. People have created VBA code that do that sort of thing, based on certain criteria.

That being said, it is generally a good idea to have an Autonumber or some other unique identifier in every table in your database.

JoeM, my projects table does not have a PK, and it is not related to any other field in other tables. It does need to be a unique identifier as it needs to keep track of new projects being added. Just confus

[June7]

Then what is the issue?

[fuecheefang]

Then what is the issue?

I have a set of numbers (project IDs) that were from an Excel Spreadsheet. We now transferred everything to Access, and need to keep those same Project ID's for historical data. We need those id's to remain while having the ability to continue adding new projects into the Access DB.

[JoeM]

So, what is preventing you from adding new Projects? You should be able to manually enter Project IDs in Access.
How were these Projects IDs being determined in Excel?
Are you looking for something automated?
What structure/rule/criteria do you want to use for these Project IDs?

[June7]

So the original ProjectNo values are just sequential numbers, no alpha characters and you want the autonumber to take over generating new ProjectNo? These ProjectNo values are unique in this table - there are no repeats?

It is possible to set the value of autonumber field with an INSERT sql action but not with an UPDATE sql action. Create a new table with the required fields then run an INSERT SELECT query to populate this new table. When the data is good, delete old table and rename new table with old name.

INSERT INTO [Copy of Table1](ID, EventNo, EID, ArrivedTime, Code, CancelledTime) SELECT ProjectNo, EventNo, EID, ArrivedTime, Code, CancelledTime FROM Table1;

Then run Compact & Repair on db before adding new records.

80+ fields is quite a few in one table. Possibly you need to normalize data structure.


As JoeM stated, autonumber is not supposed to be a value that has meaning to users. Supposedly autonumber can generate negative values and not always increasing, although I have never seen either behavior. Cannot rely on autonumber to be gap free.


Note that field names in the example do not have spaces or special characters/punctuation (underscore would be acceptable exception).

[fuecheefang]

So the original ProjectNo values are just sequential numbers, no alpha characters and you want the autonumber to take over generating new ProjectNo? These ProjectNo values are unique in this table - there are no repeats?

It is possible to set the value of autonumber field with an INSERT sql action but not with an UPDATE sql action. Create a new table with the required fields then run an INSERT SELECT query to populate this new table. When the data is good, delete old table and rename new table with old name.

INSERT INTO [Copy of Table1](ID, EventNo, EID, ArrivedTime, Code, CancelledTime) SELECT ProjectNo, EventNo, EID, ArrivedTime, Code, CancelledTime FROM Table1;

Then run Compact & Repair on db before adding new records.

80+ fields is quite a few in one table. Possibly you need to normalize data structure.


As JoeM stated, autonumber is not supposed to be a value that has meaning to users. Supposedly autonumber can generate negative values and not always increasing, although I have never seen either behavior. Cannot rely on autonumber to be gap free.


Note that field names in the example do not have spaces or special characters/punctuation (underscore would be acceptable exception).

Thanks June7, ill give this a shot and give you guys an update in a day or two. I appreciate all the quick responses.