Autonumber order in existing table (1 Viewer)

[Tee2]

I was adding an autonumber field to an existing table and I assumed the numbering would follow the order of the primary key but that doesn't seem to be the case. I was curious if anyone could shed some light on this.

 

[jdraw]

see this link for more info on autonumber

 

[gemma-the-husky]

the autonumber will follow the order of the sequence of records being added.

most of us would probably make the autonumber field the PK, and use it as the FK in other tables. It's not so important, but if you don't do this, it's hard to understand the reason for having an autonumber at all

 

[spikepl]

I assumed the numbering would follow the order of the primary key but that doesn't seem to be the case.

Define "order of the primary key" and "that doesn't seem to be the case"

 

[Tee2]

the autonumber will follow the order of the sequence of records being added.

Right, but what I was doing was adding an autonumber after records have been added.

Define "order of the primary key" and "that doesn't seem to be the case"

I'll give a bit more information so you can understand what I was doing. We are changing to Access from a different database software so I'm importing tables from that previous database. There is an existing autonumber PK from that old (non-Access) database but when you import you cannot change any field to an autonumber. I was attempting to match the old PK with an autonumber field I created in Access after the import then swap to that new field as the primary key so the old records would maintain their same primary key. The newly created autonumber field follows the order of the records most of the time but branches off at others. The old database had a few gaps in the records but I inserted dummy records into the excel export to account for this.

Whew, that's clear as mud I bet.

I found a perfectly fine workaround by making tables to match the fields of what was being imported with the addition of an autonumber field so when I imported the old PK and the new autonumber match up. I'm certain it has a method that I'm not seeing but I just could not find an explanation for it.

 

[spikepl]

The crux of the matter seems buried here:

so the old records would maintain their same primary key

What for?

 

[Tee2]

There are relationships based on those keys that will make the move to Access a lot cleaner if they stay the same.

 

[spikepl]

Access is able to propagate a change in the value of a key on which a relation relies - check the settings for a relation. So the actual value is immaterial. I don't know what "cleaner" means. It is not a database term.

 

[ButtonMoon]

There is an existing autonumber PK from that old (non-Access) database but when you import you cannot change any field to an autonumber.

What you can do is create an empty table with the autonumber column, then insert the existing primary key column into it when you insert all the other data. I'm assuming the existing PK is a number. If it isn't then maybe you could just add a sequential number before you do the insert.

 

[vbaInet]

The old database had a few gaps in the records but I inserted dummy records into the excel export to account for this.

1. Link to the Excel file
2. Create a query based on the linked table in (1) and sort in ASC by the key that you would like to use as the order for the new table
3. Create a new table with the auto number field and all the other relevant fields
4. Insert data in the sorted query in (2) into the table in (3)
5. Delete dummy records

If the table you mentioned in your first post has some changes that aren't reflected in the Excel file, then you could perform step (2) on the table and follow on from there.