Append query for One To Many Relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to append records from two tables (One to Many Relationship) into a similar set of one to many relationship tables. The table in which I would like to append the records INTO contatains an key Autonumber field for the primary key, which also relates to the many sided number field. The problem when I try to append the record to these new tables is that I get a Data type mismatch error. How do I append both tables into each new table when the One sided primary key is an Autonumber field? Thanks.
 
A couple of notions...

What business need are you satisfying by copying one set of table
information into another set of tables? There may be another solution
besides making redundant copies of information.

A data-type mismatch means one of your keys' data types doesn't match the
other. Check what types you are trying to match up.
 
I work for a professional football team. We are transferring injury data from a large program to our personal smaller program. That is what the need is for. I need to append a certain group of records Name, Background info etc., related with their injuries to our personal program. Thanks.
 
One approach would be to first append the One side data to the (new) One
side table. If your large program doesn't have a long integer type primary
key, you'll get a mismatch going to the smaller system's table with an
Autonumber.

Ditto then for the Many side table.

Is the data from both systems/programs in Access (or SQL Server, or...)?
Another possibility, depending on your need/application, would be to
construct a query/view against the data in your large program.
 
I achieved my goal in a roundabout way. First I related all of the records I needed from the large database in a query and imported it as a table into my database (both are Access databases). Then I took that table and appended the "Main" sided fields into the my "Main" table. I then took the Imported table and my Main table and related them through four separate fields to make the imported records unique (this was necessary because I was unable to append into the autonumber field) in a query. Then I was able to append the "many" sided records of the imported table into my "many" sided table by using the new Autonumber field appended to the Number field of the "Many" side. I know it sounds confusing and crazy, but it worked! Thanks for your suggestions.
 
Back
Top