How can I change a one-to-many relationship to a many-to-many relationship without losing my data already entered?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a Locations table and Contacts table.  Currently each Location can have many Contacts.  I'm now finding that each Contact can also have many Locations.  Is it possible to change this to a many-to-many relationship without having to manually reconnect the records in the join table?  How do I handle the duplicate records that are currently in the Contacts table?

Photo of Terri


  • 70 Points

Posted 3 years ago

  • 0
  • 1
The steps are more or less this. feel free to contact me via the information in my profile if you want personal help to get this data adjusted and scrubbed.

1. Create your Many to many Join Table.  The table will have no fields of its own, just the lookup fields as lookups from the parent Location and the parent Contacts table.

2. Click on the table home page icon for the join table and click import export.  Set up a saved table to table copy that you will use once.  The target table is the new join table and the Source table is the existing Contacts Table.

3. Map the [record ID#] field into the [Related Contact field], and the related location into [Related location]

4. Run the import.

5. Put the report link fields on the Contact record so the Contact shows the Locations it's used on (ie its join records),  and ditto for the Location records to show its Contacts in the Join table.

OK, so all good except that you have dups. :(  i have no idea how large your contact list is.

So there is not a lot of magic here, and I have not thought through the most efficient manual method to fix, but here is a start.

Run a summary report  on the join records by Contact name, by Record ID# of contact record.  What you want to see is that while the same contact John Smith will have multiple entries in the join table,  they will all be using the same Master Contact Record ID#, say 123.  But if there are also John Smiths who are using Record ID# 234, then you need to manually edit the Location record to use the 123 record ID# version of John Smith and no longer use the 234 version.

When you are all dine with that process there will be some contacts, such as John Smith Record ID# 234 with no children john records - ie a summary count of the # of joinn records will be zero.  Then delete them and y'ur done.