Each Procedure has a specific set of tasks that need to be completed (i.e. a set of fields that need to be annotated); the task set for each Procedure (A, B, C, or D) are completely different.

Even though every Procedure is different, all Procedures refer to the PatientID of tblMain. So, each record in each Procedure table has all the information related to that specific procedure that was performed on the patient (with PatientID).

For example, Joe Schmoe (PatientID 72) comes in on 24 March 2013 to complete Procedure 2 - the database user would open frmMain, enter Joe Schmoe's info (First Name, Last Name, DOB, SSN, Address, etc.), then select Procedure 2 from the drop down (combo box) list, then click the Continue button. At this point, I want the system to open frmB.

Upon opening frmB, the system creates a new record (say, tblBID = 46), allowing the user to complete the form fields for the CURRENT INSTANCE of Procedure 2 performed on 24 March 2013.

So, upon completion of frmB, tblMain has record 72 that shows the patient info, and tblB has record 46 that refers to PatientID = 72.

The patient is entered only once, but each patient could have multiple Procedures done (which means that multiple records from various Procedure tables could refer to one patient) - each Procedure has a specific date and outcome, but can only refer to one patient.

I may have (obviously) over-blown this whole process - PLEASE let me know if there's an easier way to do this.

---

Reply from Richard Rost:

This is OK, but there are better ways to do it. For example, I would have a SINGLE procedure table, instead of 4 different tables and forms. If each of the procedures has different steps, then you can specify those in a 3rd table. This way you're not redesigning the wheel 4 times.