Need to export one table to another table (1 Viewer)

RonW7341

Registered User.
Local time
Today, 18:43
Joined
Aug 16, 2011
Messages
18
I have two tables of different structures. Both have sys=autoincrement as a unique record identifier. What is the best way to blend the two tables into one? Should I change the field structure of each table so that they match up for a file export and import and if so how do I handle the fact that each table currently has it's own record identifier number.? Thank you very much for your help. ron
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:43
Joined
Jul 9, 2003
Messages
16,245
There are three main issues:-
1) If the tables are matched against records in other tables then you need to preserve this relationship.

2) If you are going to append the tables into one new table then you may have a problem if a record from each table has the same record number. if you had a record number 1287 in each table, then after appending you would have two records with the same record number, which would not be good!

3) You will also require the new table to have a unique Auto number field. (A unique value for each of the records from the previously separate tables)

a) First thing to do is make a backup of your database and don't touch it!
b) Next you need to create a new field in each table to record the current Auto number identity field {for BOTH Tables}.
c) Now add another field, the purpose of this field is to record the identity of the two tables and preserve those identities in the new combined table. It could have a text value like "table 1" and "table 2" or you could have a numeric value like "1" & "2" (this would be the best option).
d) Create a new table, it must have an auto-number field with a unique ID.
e) Create all of the fields necessary to harbour all of the information from the previous two tables.
f) Append the information from the two separate tables into this new table.
g) Examine your other tables and identify the fields which contain the ID that matches the old unique ID from the two tables.
h) Create as many update queries as necessary to change the ID in each of your other tables to the new newly created unique record number field.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 22:43
Joined
Feb 19, 2013
Messages
16,553
assuming your autonumber fields are duplicated in each table (i.e. both start from 1) you have a number of choices. If the ID field is being used for what it is intended - a field identifier, then it shouldn't matter, just append one table to the other. Note if the ID field is referenced by other tables (perfectly legitimate) then you would need to update the other tables with the new ID

Alternatively, create a new table with two additional fields - an 'oldID' field and an 'oldtableID' field to identify where the data came from. Once you have sorted out any issues with the combined data these fields can be deleted.

As to which is the best way - it depends on your existing structure

Dang: beaten by Unc
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:43
Joined
Jul 9, 2003
Messages
16,245
Actually thinking about it you don't need to b) I was thinking of something else when I wrote that.
 

RonW7341

Registered User.
Local time
Today, 18:43
Joined
Aug 16, 2011
Messages
18
Thanks to both you guys for responding so quickly. Thinking through what you've sent me raises another question. If each table has the same numbering pattern, 1-270 as an example. Is there a way to go in and have the autoincrement change one of the tables to begin at 600-870 as an example and leave the other one as is. That way the blending of the two should not be a problem since they are now unique. After the blending occurs, do the same thing again and number the entire new table with 1-540 for example? Is that possible and if so can you give me the steps? Thanks a bunch. ron
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:43
Joined
Feb 19, 2013
Messages
16,553
It is not possible on an autonumber field as such but if you were to create a copy of the table structure, the autonumbers will start from 1 again so append the first table and then the second.

Alternatively revisit my second paragraph
 

Users who are viewing this thread

Top Bottom