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?