Adding Existing Numbers with Auto Number (1 Viewer)

gsrajan

Registered User.
Local time
Today, 09:34
Joined
Apr 22, 2014
Messages
212
I have a table (Table A ) with 3000 records. Primary key is auto number. The company already has another table (Table B) with around 6000 records, with primary key -Numbers. The numbers in Table B are manually entered, not auto numbers. The company wants to retain the existing numbers in Table B for a valid reason. I need to combine both tables ( columns are same ) and going forward, the number should be auto number for new records. Please let me know whether it is possible. Please guide me how to do that if possible.

Thanks for your help.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 15:34
Joined
Apr 27, 2015
Messages
2,848
If I understand you right, you can get rid of the Autonumbers in Table A?

If so, do an Append query adding Table A to Table B.
 

Minty

AWF VIP
Local time
Today, 14:34
Joined
Jul 26, 2013
Messages
6,962
You would have to ensure that the number ranges don't overlap, is that the case?
If it is then you can reseed the auto number using a variety of techniques.

If not then that's a whole different ball game.
 

theDBguy

I’m here to help
Local time
Today, 06:34
Joined
Oct 29, 2018
Messages
10,019
Hi. You said you need to keep the numbers in table b, but what about the autonumbers in table a, do you need to keep them also? If there are duplicate values between the numbers in table b and the autonumbers in table a, then you can't use the autonumber field as a primary key.
 

gsrajan

Registered User.
Local time
Today, 09:34
Joined
Apr 22, 2014
Messages
212
Thank you for your replies. No, I don't have to keep the auto numbers in Table A. But how do I make the new record with Auto number. No, there are no duplicate values between these two tables. There are no overlapping numbers,. I can use the append query if I can use auto number going forward. Thank you, please let me know how to use auto number going forward.
 

gsrajan

Registered User.
Local time
Today, 09:34
Joined
Apr 22, 2014
Messages
212
Thank you everyone. I will backup tables and sue the append query. Thanks again for your time.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:34
Joined
Feb 19, 2002
Messages
28,734
If the columns are the same, append tableB to tableA. That will retain autonumber as the PK but append existing values from tableB. This assumes there are no overlaps in the PK values of the two tables.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom