• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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?
  • 2
1 Solution
Jeffrey CoachmanCommented:

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.

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.
Jeffrey CoachmanCommented:

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now