Convert Long Integer Number to Autonumber

[Rx_]

Convert Long Integer Number to Autonumber [Solved]

Tried to convert a Long Integer column to Autonumber and get the error:
"Once you enter data in a table, you can't change th edata typ of any field to AutoNumber, even if you haven't yet added data to the field."

Just normalized many tables into one table by turning off the Autonumber.
Used a Hashing method to keep track across the many updates.

The field that I need to change back to an autonumber starts at 1,000 to 1,832 - has gaps (tht was the first table), then 2,000 to 2,304 - has gaps (that was the second table) ... and so on.

All I need to do now is to convert my first column (Long Integer) back to an Autonumber - and keep the data carefully migrated in.

 

[Rx_]

WHEW!
solved my own problem.
Basically: Appending numbers into a table with an autocounter - will keep the number appened in, while copy and pasting data - the autonumbering will take up where it left off.

Had a table with no autonumber (Long Integer) with Hashed keys for a normalization effort.
Created a copy of the table (Structrure Only) with no data. Changed the data type to an Autonumber.

1. Tried to copy and paste all data - Failure
The contrived numbers in my first column were replaced by the auto-number starting at 1
2. Create an Append Query to the new table with Autocounter - Success
The contrived numbers match exactly and the next record is an autonumber based off the last highest record.

In my data migration, I did happen to keep my new primary ID keys in sequential order.

Hope this can help someone else.

Summary: For a data normalization effort, the sequential Autonumber could not be used.
Clone a copy of the table to receive the data (clone with no data).
Turn off autocounter.
Append the data from multiple tables into it - in sorted order.
The first column will later become the autocounter field.
Example: The data use to have customer ID's, with one table for each state. Each state had a autocounter field starting at 1.
The custer ID linked to a OrderID (one table per state) that also started with 1
For Migration - contrive a number for both CustermerID and OrderID so they will re-match up later.
When Migrating data into a single table - renumber the customer ID.
This table is a Clone with the autocounter turned off. Create the numbers needed for migration. (note: keep sequence order for when the autocounter is turned back on)
CustID State
1001 1
1002 1
1003 1
2001 2
2002 2
2003 2
3001 3
3002 3

A Clone of this table - with the autocounter turned ON the first column (CustomerID) is created. The Autocounter is turned on for column 1.

If we try to copy and paste the data above (no autocounter) into an empty clone with the autocounter turned on, the autocounter doesn't like your numbering.
But, if we append the data above into the clone... no problem. And, the autocounter is ready for the next number (i.e. 3003).