4 Replies Latest reply on May 2, 2009 5:13 PM by gdaymte

    Setting up relationships between 2 tables



      Setting up relationships between 2 tables


      I am using FM 9 Pro on Win XP Pro SP2. I have searched around the forum for info about joins, portals, etc., but having found really what I am looking for.


      I have 2 tables:

      1) PatInfo --> PatId, Lname, Fname, LabId1, LabId2, ResultA, ResultB, ResultC, ResultD

      2) ResultInfo --> LabId, ResultA, ResultB, ResultC, ResultD


      The PatInfo table contains one record for every patient. Each patient could have up to two LabIds. Results A-D in this table are basically a summary (determines if the patient was ever postive for Results A-D). Results A-D needs to be calculated by values in the ResultInfo table.


      The ResultInfo table contains a record for every specimen received by the lab. PatId is not a part of this table, only the LabId. Each LabId has its own positive/negative value in the Results A-D fields.


      How would I go about joining these 2 tables to get the information I need?

        • 1. Re: Setting up relationships between 2 tables
             May I ask what the purpose of the LabID is? Can there be more than 2 labID?
          • 2. Re: Setting up relationships between 2 tables
               The purpose of LabID is to track each specimen that comes into the lab. A person can have up to 2 specimens submitted for this project.
            • 3. Re: Setting up relationships between 2 tables

              Well normally in cases as such you would have a third table that stores the patientID and the LabID. I would recommend this kind of setup if you plan on having more than 2 records or the possibility of having more than 2. That would be the correct way to go.


              However, if you are stating that you have will only ever have 2, to make things easier, you may be able to just use your current two tables as long as the LabID will always be unique in the patient table. You could just join the two tables based on the LabID fields. You would need 3 relationships though for this setup. One for the first LabID, one for the second, and one to give you the sum of both. You can do this third relationship by creating a calc that uses List ( LabID1; LabID2 ) in the patient table and joining it to the LabID in the ResultInfo table.


              Furthermore, you dont really need to have ResultA, ResultB, ResultC, ResultD in the patient table. If you create a summary field for each of those ResultA, ResultB, ResultC, ResultD field in the ResultInfo table, then you can just reference those fields ( for each of the 3 relationship created earlier 3 X 4 = 12 ) on the Patient layout and it will give you the sum of the lab results.


              Again its up to you which road you would like to take.

              • 4. Re: Setting up relationships between 2 tables
                   Thanks...I will give that a try.