Hello,

Sorry to put this in Value Lists, since it is much broader.

I wish to build a file that lists what nurse works for a particular doctor, and what facility they work at. A doctor can work at multiple facilities, and a nurse can work for multiple doctors but only one facility. It would be super great if I could pull up a facility, and have it list which nurse works at that facility and for which doctors, or pull up the doctor and list what nurse works for that doctor and what facilities they work at, or pull up the nurse and list what facility and doctors they can work for at that facility.

I have no clue where to begin!

TIA

edit: Maybe this would help someone help me. The list I currently have (edited, of course!):

• Dr. A
  
  • Facility 1
    
    • Nurse Jane
      
    • Nurse Cindy
      
    • Nurse Mary
      

    [*]Facility 2

    • Nurse Tyrone
      
    • Nurse Katrina
      
    • Nurse Andy
      

    [*]Facility 3

    • Nurse Fred
      
    • Nurse Tupak
      
    • Nurse Shelley
      

• Dr. B
  
  • Facility 2
    
    • Nurse Tyrone
      
    • Nurse Andy
      

    [*]Facility 3

    • Nurse Tyrone
      
    • Nurse Katrina
      
    • Nurse Andy
      
    • Nurse Bobbie
      
    • Nurse Felicity
      

    [*]Facility 5

    • Nurse Johnny
      
    • Nurse Cheryl
      

• Dr. C
  
  • Facility 1
    
    • Nurse Jane
      
    • Nurse Mary
      

    [*]Facility 6

    • Nurse Gizelle
      
    • Nurse Poppy
      
    • Nurse Frank
      
    • Nurse Guiseppe
      
    • Nurse Henrietta
      

You will need four tables, related as:

Doctors -< DoctorNurses >- Nurses >- Facilities

where DoctorNurses is a join table with a separate record for each association of a nurse to a doctor.

Your report would be produced from the DoctorNurses table, sorted by doctor and by facility (with sub-summary parts for each).

You will need four tables, related as:

Doctors -< DoctorNurses >- Nurses >- Facilities

where DoctorNurses is a join table with a separate record for each association of a nurse to a doctor.

Your report would be produced from the DoctorNurses table, sorted by doctor and by facility (with sub-summary parts for each).

Thanks. That unfortunately doesn't mean a whole lot to me but it sounds like a good starting point. I'll try it out!

Have a look at the demo here:

http://www.fmforums.com/forum/showpost.php?post/246136/

This should explain how to relate doctors to nurses. Add a Facilities table and associate each nurse with a facility.

Have a look at the demo here:

http://www.fmforums....hp?post/246136/

This should explain how to relate doctors to nurses. Add a Facilities table and associate each nurse with a facility.

Thanks for all of your help. I love your example but adding the third table into the mix is confusing me. Does this look right?

You need a FacilityID field in the Nurses table. This will hold the ID of the facility the nurse works for. The relationship needs to be:

Facilities::FacilityID = Nurses::FacilityID

You need a FacilityID field in the Nurses table. This will hold the ID of the facility the nurse works for. The relationship needs to be: Facilities::FacilityID = Nurses::FacilityID

That works! I actually get it now. Thanks so much.

Now I just need to figure out the report thing you had mentioned. And how to attach a scanned pdf that proves each affiliation.

We are told that a nurse can only work at only one facility. But, unless there's typos in the above listing, I'm confused why the relationship would not be the following, since, for example, Nurse Tyrone (Katrina, and Andy) can be related to more than one facility (Facility 2 and 3):

FACILITY ——< NURSE_FACILITY >—— NURSE ——< NURSE_DOCTOR >—— DOCTOR

?

Because a nurse can work at only one facility (we are told).