Appending tables with one-to-many relationships?

[RSW]

Hello,

I have two auto-numbered tables, each with one-to-many relationships with a couple of other tables.

The two auto-numbered tables have identical column properties, as do the tables each are linked to.

How can I set up an append query to append records from one of these tables into the other, but ensure that all of the links stay intact? (Right now, the two tables duplicate ID numbers.)

I hope this makes sense. Can anyone assist?


Thanks in advance.

 

[Uncle Gizmo]

If I understand you correctly you have two Identical (layout) pairs of tables linked one to many. So in effect you have two separate sets of records in four tables.

You want to move the contents of one set of tables (linked one to many) into another set of tables (linked one to many)

I am guessing the problem is in both sets the master table for each set contains a unique ID (auto number) however this number is sometimes the same in both sets of data, so that if you combine the two sets of data together you end up with a duplicate Value which will cause you problems. have I interpreted your problem correctly?

 

[Cremate]

Hello,

I have two auto-numbered tables, each with one-to-many relationships with a couple of other tables.

The two auto-numbered tables have identical column properties, as do the tables each are linked to.

How can I set up an append query to append records from one of these tables into the other, but ensure that all of the links stay intact? (Right now, the two tables duplicate ID numbers.)

I hope this makes sense. Can anyone assist?


Thanks in advance.

Id rethink your DB structure. If you have identical properties (Assuming fields) in 2 tables and you want to copy from one into the other you are duplicating lots of data.

If you want to access data from another table, either create a lookup table or add a column in one of your tables with a foreign key into the other table (referencing its ID).

 

[RSW]

If I understand you correctly you have two Identical (layout) pairs of tables linked one to many. So in effect you have two separate sets of records in four tables.

You want to move the contents of one set of tables (linked one to many) into another set of tables (linked one to many)

I am guessing the problem is in both sets the master table for each set contains a unique ID (auto number) however this number is sometimes the same in both sets of data, so that if you combine the two sets of data together you end up with a duplicate Value which will cause you problems. have I interpreted your problem correctly?

That's exactly it.

 

[RSW]

Id rethink your DB structure. If you have identical properties (Assuming fields) in 2 tables and you want to copy from one into the other you are duplicating lots of data.

Well, I want to consolidate the two tables into one (the way the database should have been designed originally)

 

[Cremate]

Well, I want to consolidate the two tables into one (the way the database should have been designed originally)

http://www.sqlteam.com/article/using-select-to-insert-records

You might also be able to use a union with two selects

select * into newtable from tbl1
union
select * into newtable from tbl2

Not exactly sure if that would work, you may have to wrap that in another select.... the first solution might be best (via the link)

 

[Cremate]

Apparently you can union... Take this example I found....

SELECT pub_id, pub_name, city INTO results FROM publishers
UNION
SELECT stor_id, stor_name, city FROM stores
UNION
SELECT stor_id, stor_name, city FROM storeseast

 

[Uncle Gizmo]

That's exactly it.

in that case you need to choose one set of data to work on, add a field to each table in the set, then copy into this new field the ID of each table where appropriate. (backup first!)

Now you have preserved the linking information, you have several options for modifying the two new fields.

The object is to create a set of data that is different to the set that you intend linking to.

One way is to add 1000 or 10,000 to the existing records forcing the range of data in to a completely different range of data than in the other set.

Now you will be able to see that you can add this new data to the other data, however you need to change the auto number field into a basic number field or the data will not transfer correctly.

Once you have added the data together, then you can create a new auto number field, then match this new value to the values in the one to many linking table, delete all the redundant fields and you should have a set of data that you can use.