In addition to linking patients to their medical history, you will likely need this relationship:
Patients::PatientNumb = Patient_Procedure::PatientNumb
Procedures::ProcedureID = Patient_Procedure::ProcedureID
You'd define one record in Procedures for each procedure this medical practice can perform.
Patient_Procedure would document what procedure was performed on a given patient. This is called a many to many relationship with Patient_Procedure functioning as the Join table linking the two tables.
A portal to Patient_Procedure can be placed on a Patients layout that includes fields from Procedures to list the procedures performed on that patient. A portal to Patient_Procedure can also be placed on a Procedures layout that includes fields from patients to list all the patients on which a given procedure has been performed.
My original suggestion was to only link Patient and Procedure to Patient_Procedure. PatientID would be a serial number defined in Patient. ProcedureID would be defined in Procedure. Procedure records would link to Patient_Procedure only by the ProcedureID number.
Example: Say one of your procedures is "Colonoscopy". It might have the serial number 235 in the Procedure table. If Patient 123 recieves a colonoscopy, a new record would be created in Patient Procedure with 123 in PatientID and 235 in ProcedureID. Thus, this new record records the fact that Patient 235 received a colonoscopy. Data, such as the date when the colonoscopy was performed on this patient, would be stored in fields defined in Patient_Procedure. Data common to all colonoscopies would be recorded in fields defined in the procedures table.
With regards to the other tables you are linking to Patient_Procedure:
I have no idea what AE is and so can't say anything about it one way or the other.
I would not link Med. Hist. to Patient_Procedure unless I planned to create a series of records for the same patient--one for each procedure performed. If that were the case, I'd probably dispense with the History table completely and just put those fields in Patient_Procedure.
Linking the Conc Med table would make sense if you wanted to see a list of all meds linked to the procedure for which they where prescribed. That would require a different link, however. I'd define a third field, Patient_ProcedureID in Patient_Procedure as an auto-entered serial number and use it to link to the Conc Med records so that I can see a list of Med records for each Procedure performed on a given patient. If you only want to link the records by PatientID, I'd link this tale directly to the Patient table.
Here's download link to a demo file you can examine. It matches "contracts" to "companies", but if you were to rename them Patients and Procedures, it would provide a decent example of what I described in my original post to this thread.
thank you for the example and the information.
The AE and Med History table should only be there so I can check the medical history of one patient based on the patient number. Thanks a lot for your help!