Okay I need to have a database that is very similar to the standard student-teacher database. I have tblPatients (patientID, patientFName, patientLName, CounselorID(FK), and CoordinatorID(FK)), tblStaff(staffID, staffFName, staffLName) and a few other unimportant at this time tables. My problem is each patient has a coordinator and a counselor(which may or may not be the same person) and each staff member can be a counselor AND/OR a coordinator. I want to be able to make a query that displays StudentFName, CounselorName, and CoordinatorName.
So far everything that I have tried has either given me the same name for both the coordinator and the counselor(which is okay for some but not most) or I get an error saying mismatched expression. I am assuming my relationships are wrong but Ive tried it so many different ways and no victory. As of right now I have CounselorID and CoordinatorID related to StaffID.
HELP?!?
Thanks!