Merge 2 Tables

[Rundog]

Hi, I got two tables that are Identicle in structure down to the last speck of dust. In one table I have lots of data in the other I have nothin' ..."so what's the deal" (I can hear ya right now! )...

Table A is populated with loads of data, the primary key is the RecordNumbers.
Table B ...zilch, nothin, zero except for the primary key (RecordNumbers)
That goes from 1 to 10100

The record numbers in A are in sequence 1 , 2, 3 etc to about 10 000 but have gaps
so it goes 1,2,3,5,9,24,50 etc

The numbers in B are in sequence but no gaps. 1 to 10 000 consecutively

How do I get all the records in A over to B so that the gaps are filled with a RecordNumbers (Complete with no data(yet))

It would now look like this..
1 data
2 data
3 data
4 no data
5 data
6 no data
7 no data
8 no data
9 data
& so on

Rather then this...
1 data
2 data
3 data
5 data
9 data
& so on

I am just trying to add record numbers where there should be a number instead of a skip from say 1,3,12

Ideas folks? Oh by the way I want it to be a permanent change.

AndyPandy

 

[sfreeman@co.mer]

If you must...

There is really no point to trying to keep your autonumber field values sequential, but if you must...

Do this only on a backup copy of your data to make certain it works as expected.

This also assumes that that autonumber value is NOT participating in any relationships... if so, you will need to add an update query to update any related tables that rely on the autonumber field as a foreign key.

Open the primary table in design view and add a new field beneath the autonumber field called OldValues, number type.

Save the table, open it in datasheet view, and select the entire original autonumber field, and copy the data, and paste it into the new field. Make sure that every record has copied the original autonumber value into the new field.

Open the primary table in design view and delete the autonumber field. Save the table. Re-open in design view and add the autonumber field back again. Save the table.

You should now have a new autonumber field with all sequential values, and the new number field that contains all of the original autonumber values.

Double check to make sure that both the new autonnumber field and the OldValues field are both sequential, at the same time.

If so, you can delete the old values field... and you can delete that other table that you have with just the autonumber values 1-10,000...

Simple. ;-B

 

[Rundog]

The RecordNumbersID field is the primary key and only identifies the row, It is just that not an autonumber. I simply want to have my key run consecutively from 1 to 10100 with no breaks in the numbering, this will leave some rows with a primarykey number but no data as yet, I fill in the data in the empty slots later - They are in effect vacant slots awaiting to be filled.

One table (B) is populated only by the consecutively numbered primary key with no breaks in the sequence - I just need to add this row to the table that has breaks in the sequence, overwriting numbers that already exist in table (B) with rows that have data in them thus creating a continuos set of primarykey numbers, none repeated (Obviously) because those that exist in both tables are overwitten by the one that contains data in (A)

 

[Rundog]

How totally rude of me - I should thank you for your imput thus far, Thanks peeps

 

[Rundog]

I made a mistake if you downloaded the tables before...this one is the tables as they actually are - unmodified.

Please take into account that I am not good with querries yet so make any answer easy for me to use ... thanks
Andy

TwoTables Example (zip) new version

 

[Rundog]

Hi Pat...No I didn't, not for lack of trying...I don't think it's your method that is wrong, I am absolutely sure it's my lack of undertanding at the mo...

 

[Rundog]

I got it now - Problem solved - end of thread - Thanks for the shove in the right direction pat

Diolch yn fawr!
Andy