Preserving record ID when converting to auto number

[Guest]

Hello -

I have three tables that are linked to eac other by ID numbers. Example,
Family Table has an ID, and the children have their own ID, but are linked to
the Family via the family ID.

The IDs are currently a NUMBER field. I need to import these 3 old tables
into new tables where the ID will be an auto number field.

Do you have any tips on how I can get the data into the new tables and
preserve the old ID number as it converts to an autonumber?

The only way I can think of is to put the data into excel, create a blank
record for each ID number that is missing between 1 and the highest number,
importing the full sheet, then deleting the blanks. [ I could use a VLOOKUP
to make this faster]

If you have a more elegant strategy, that will be greatly appreciated.

thanks
sandy

 

[Joan Wild]

You can use an append query; include your current IDNumber field, and choose to append it to the autonumber field in your new table. Records you add after this will use the next highest number for the autonumber. (You shouldn't care what the value of the autonumber is).

 

[Guest]

So if I understand correctly, if you append a NUMBER field to an AUTONUMBER
field in a new table, the AUTONUMBER field takes on the original NUMBER field?

Thanks!
sandy

You can use an append query; include your current IDNumber field, and choose to append it to the autonumber field in your new table. Records you add after this will use the next highest number for the autonumber. (You shouldn't care what the value of the autonumber is).

--
Joan Wild
Microsoft Access MVP

Hello -

I have three tables that are linked to eac other by ID numbers. Example,
Family Table has an ID, and the children have their own ID, but are linked to
the Family via the family ID.

The IDs are currently a NUMBER field. I need to import these 3 old tables
into new tables where the ID will be an auto number field.

Do you have any tips on how I can get the data into the new tables and
preserve the old ID number as it converts to an autonumber?

The only way I can think of is to put the data into excel, create a blank
record for each ID number that is missing between 1 and the highest number,
importing the full sheet, then deleting the blanks. [ I could use a VLOOKUP
to make this faster]

If you have a more elegant strategy, that will be greatly appreciated.

thanks
sandy

 

[Joan Wild]

Yes. If you're leary, just backup your existing database and give it a try (always a good idea anyway when doing any mass update/change)

--
Joan Wild
Microsoft Access MVP

So if I understand correctly, if you append a NUMBER field to an AUTONUMBER
field in a new table, the AUTONUMBER field takes on the original NUMBER field?

Thanks!
sandy

You can use an append query; include your current IDNumber field, and choose to append it to the autonumber field in your new table. Records you add after this will use the next highest number for the autonumber. (You shouldn't care what the value of the autonumber is).

--
Joan Wild
Microsoft Access MVP

Hello -

I have three tables that are linked to eac other by ID numbers. Example,
Family Table has an ID, and the children have their own ID, but are linked to
the Family via the family ID.

The IDs are currently a NUMBER field. I need to import these 3 old tables
into new tables where the ID will be an auto number field.

Do you have any tips on how I can get the data into the new tables and
preserve the old ID number as it converts to an autonumber?

The only way I can think of is to put the data into excel, create a blank
record for each ID number that is missing between 1 and the highest number,
importing the full sheet, then deleting the blanks. [ I could use a VLOOKUP
to make this faster]

If you have a more elegant strategy, that will be greatly appreciated.

thanks
sandy

 

[David W. Fenton]

So if I understand correctly, if you append a NUMBER field to an
AUTONUMBER field in a new table, the AUTONUMBER field takes on the
original NUMBER field?

An Autonumber is not really an identity key at all. All it is is a
special kind of default value for a long integer field. The only
difference is that you can't change the value once the field has
been populated. That's why you can append any value to a new record
(as long as it doesn't collide with an existing value), because when
the value for the field is already supplied, the default value does
not kick in.

I *really* wish the Autonumber had been implemented as a type of
default value instead of the way it is. That would make it *much*
clearer to everyone what is going on and obviate all sorts of
misunderstandings.