What is the best way to import external data into a linking table that has multiple one-to-many relationships?

I understand that there is no way to import text or spreadsheet data to a linking table that has multiple one-to-many relationships (see the diagram of the relationships on the attachment--note the CompanyContacts table). So, if using the external data import function in Access 2007 will not work, what other method(s) could be used to import external data into the CompanyContacts table? The data that I need to import to CompanyContacts causes key violation errors with both the Companies and the Contacts tables. Both Companies and Contacts uses a single primary key (each) that is set to Autonumber, duplicates are not allowed. When I tried to import the data to the CompanyContacts table, I got the expected key violation error message. I have too much data for manual entry, so how could I import external data to the CompanyContacts table?
relationships.pdf
jfikesAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
jfikes,

You can't add records to the many side of a relationship before you add the corespondig "One" record.
(For examplel: You cannot import Orders for customers who do not exits yet.)
So you must import the One records (New Customers) first.

So it can be done, but you just have to do this in the correct order.

It also might be difficult if you relatinships are complex.

What is typically done is to import the data to "temporary Staging" tables, which will be stand alone tables.

When all the data is consistient, you can append the records to the linked table.

Jeffcoachman
0
 
jfikesAuthor Commented:
This worked. I made sure that the two primary keys in the linking table were set to allow duplicates. I used an append query to move the data from the external files to temporary tables. When I ran the append queries, no error messages occurred -- and everything works perfectly. Thanks for the excellent advice.
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.