Complicated relationships between related tables
I am trying to create a database that automatically creates separate tables based on a patient's status. For example, I have one table with all the biographical information related to a patient, another with insurance information, etc. One of the fields related to the patients' unique identifier is their status, in my case - their transplant status i.e. "transplant complete," "considered for transplant," "rejected for transplant," or "unconsidered for transplant."
For each status, their are associated tables specifically related to that status, for example if their status is "transplant complete," there are several related fields: "date of transplant," "type of transplant," etc.
Does anyone know how I can make that relationship that is based off the patient's unique identifier and their status? Can I just make two conditions that need to be met in the relationship before it relates to the other tables?
Thank you for your help!