I need a function that keeps only like names when comparing two records

[ork2002]

So I have a table of surgical procedures(field = "procedure"), each procedure has a multi-value list of people qualified to perform this procedure (multivalued field = "surgeons").

In my main table I have a list of patients, each of which may have up to 8 OR procedures performed. Using a join query, I can see a total list of all surgeons that could perform each procedure on a select patient; but I need the opposite of a "select distinct" function in order to display only the surgeons that can perform all the procedures.



For example: Patient A requires both procedure1 AND procedure2:

Procedure1: Surgeon1, Surgeon2, Surgeon3
Procedure2: Surgeon2 only

I need the opposite of a select distinct function that will show only surgeons who are able to perform both procedures: In this case:
Patient A; Surgeon 2

And as I said, this needs to accommodate up to 8 procedures for each patient.

Any ideas. I am thinking that this will require a user-defined function to accomplish. Thanks!

[orange]

Most developers will tell you to avoid
1) multivalued fields
2) table fields with lookup at the field level

Build tables:

Surgeons and Procedures ===> but this will indicate a surgeon can perform Many Procedures and
A Procedure can be performed by Many Surgeons.
Many Surgeons can perform Many Procedures
So you need a linking/mapping/junction table to resolve which Surgeon can perform which Procedures

To resolve/avoid/work with Many ---------->Many relationship, you use a junction table to build
two (2)   1 to Many relationships.

1-------------------->Many                        Many<------------------------------1
tblSurgeon --------->tblSurgeonPerformsProcedure<-----------------------tblProcedure

Watch this video.

[ork2002]

So I have fixed this by using several queries. The first query pulls pulls all the faculty associated with each procedure and counts them, ie

Patient unique identifier, patient name, Faculty name, Faculty count
#######, ######, Faculty1, 3

The second query counts the total number of procedures listed under each patient OR booking. total procedures = 3

The third query looks at each of the above two queries and keeps only the faculty names who's Facutly count = total procedure count and there it is. A list of all the faculty who can perform all of the individual procedures listed for the OR booking. Solved.