posternutbag Posted August 10, 2011 Posted August 10, 2011 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
comment Posted August 10, 2011 Posted August 10, 2011 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).
posternutbag Posted August 16, 2011 Author Posted August 16, 2011 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!
comment Posted August 16, 2011 Posted August 16, 2011 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.
posternutbag Posted August 17, 2011 Author Posted August 17, 2011 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?
comment Posted August 17, 2011 Posted August 17, 2011 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
posternutbag Posted August 18, 2011 Author Posted August 18, 2011 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::FacilityIDThat 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.
eibcga Posted September 2, 2011 Posted September 2, 2011 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 ?
comment Posted September 2, 2011 Posted September 2, 2011 Because a nurse can work at only one facility (we are told).
Recommended Posts
This topic is 4850 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now