2 Replies Latest reply on May 11, 2013 5:56 AM by illumejames

    How to Correct a MANY to MANY Relationship?

    illumejames

      Hello all,

       

      Here is my problem:

       

      I inadvertently connected two tables together via their FK's.

       

      [Pet Table] Pet PK

      [Pet Table] Pet Owner FK ------> [Service Given Table] Pet FK

       

      The Pet Table host a portal whose records are stored in [Service Given Table].

      The portal is working fine expect in the cases where the Pet Owner has multipule

      pets; in that case any "service" entered for one pet is added to every pet because

      of the Many to Many relationship.

       

      I understand relationship must be, and should have been One to Many.

       

      [Pet Table] Pet PK ------> [Service Given Table] Pet FK

      [Pet Table] Pet Owner FK

       

      When I change the relationship the path is broken and the portal records

      no longer show in the [Pet Table]. This is because the data in the [Service Given Table] still shows

      the Pet Owner FK data. I have manually changed the Pet FK [=Owners Name] data to Pet PK and

      the portal relationship is restored.

       

      What I need to know is how to go about doing this via a script which changes all

      1,500 records. Note: In the [Service Given Table] exsist both the Pet Owner Name

      and the Pet's Name … using a combination of the two, I have successfully been able to use PERFORM FIND

      in the [Pet Table] to find unique records and therefore copy the PET PK and place it in [Service Given Table] Pet FK.

      What I have not been able to do is write a script which does this properly. Everything I've tried fails when it reaches

      an owner with multipule pets.

       

      Example:

       

      BEFORE SCRIPT

       

      [Service Given Table]

      Ms. Moss | Dog | Poly

      Ms. Pruitt | Dog | Ipa

      Ms. Evan | Dog | King

      Ms. Evan | Dog | Tracy

      Ms. Evan | Dog | Bruno

      Ms. Evan | Dog | Sage

       

      AFTER SCRIPT

       

      [Service Given Table]

      45 | Dog | Poly

      46 | Dog | Ipa

      47 | Dog | King

      47 | Dog | Tracy

      47 | Dog | Bruno

      47 | Dog | Sage

       

      Note: All pets in [Pet Table] have unique IDs.

       

      Any help would be appricated! [On a tight deadline]

        • 1. Re: How to Correct a MANY to MANY Relationship?
          deninger

          There are a couple ways to handle things like this. One would be to add a second key pair (pet fk=pet fk AND owner fk = owner FK)  to the join relationship assuming pet owner fK could appear I the service given table. One could also use a third join table (pet_service table join), though this requires other compromises.

          • 2. Re: How to Correct a MANY to MANY Relationship?
            illumejames

            Thanks Deninger. I figured something else out. Using a script which included two unique fields within the Services Given table, I serached the Pets table for the PK value of each pet – copied that value and pasted it into a new empty field [NewFK] withing the Services Given table. After this was complete I was able to correct the relationship link and everything works fine now. Thanks again.