Adding Existing Numbers with Auto Number (1 Viewer)

[gsrajan]

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]

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]

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]

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]

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.

 

[Minty]

This thread will assist https://www.access-programmers.co.uk/forums/threads/pasting-data-with-autonumber.123006/
- I think you will need to create a new table, and append the existing records (Lowest Number field first) then the second table.

 

[gsrajan]

Thank you everyone. I will backup tables and sue the append query. Thanks again for your time.

 

[theDBguy]

Thank you everyone. I will backup tables and sue the append query. Thanks again for your time.

Okay, good luck. Let us know how it goes.

 

[gsrajan]

Sure I will. Thank you.

 

[Pat Hartman]

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.