Here's my problem.
I am creating a solution that tracks surgeries. For each surgery, I want to be able to record not only the type of surgery and the patient, but also the surgeon, the circulating nurse, the scrub nurse, the anesthetist, etc.
In the solution I have a table called "personnel" that has all of these folks in it, with a field, TypeOfPersonnel, that has either surgeon, nurse, scrub, or anesthetist.
What I want to be able to do is create a value list of just surgeons, just nurses, or just anesthetists, without having to have separate tables for each type of personnel- because sometimes (e.g. when a circulating nurse on one case can be a scrub nurse on another) they can be in several categories.
Once I do this, I figure I can make drop-down lists for each field in my patient registration table that will be populated from one of these value lists. But I am stumped as to how to do it. I have played around with self-joins, but that hasn't worked. I am sure this has an easy fix- can someone help me out? Thanks.