Results 1 to 8 of 8
  1. #1
    jice89 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2011
    Posts
    7

    Student database....kinda

    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!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post a picture of your relationships and tables.

  3. #3
    jice89 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2011
    Posts
    7
    It's ugly...

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    what are you relevant entities, jice??

    If this is a simple "student is taking class from a life-experienced person and being called a 'patient' scenario, my opinion on what it should look like is:

    STAFF
    id (PK)
    first
    last

    PATIENT
    id (PK)
    first
    last
    coordinator assigned (FK, coordinator or staff table)
    counselor assigned (FK, counselor table, if there needs to be one)

    ENROLLMENT

    patientID (FK, patient table)
    classID (possible FK from classes, but it's not necessary unless the situation would call for it later on).
    comments

    CLASSES
    id (PK)
    ALL OTHER
    room (FK - room table, but the room table is not needed, based on your image)

    THE Junction table I don't understand, but then again, I do whatever I can to avoid this type of thing, and also composite keys. I would doubt you'd need a composite key anywhere in this.

  5. #5
    jice89 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2011
    Posts
    7
    If I do it this way, because this is how I originally tried it, I can't show different faculty and coordinators for each patient when I query. It always shows the same name in each field or no names at all. This isn't quite a student database, just similar to one. This is for a hospital where the patients take classes and have counselor, coordinators, and an instructor for each class. Would it be easier to have three different tables for coordinator, counselor, and faculty? My only problem with this way is that it is time consuming to have to enter each staff member into these tables three times. Thanks for you help!

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by jice89 View Post
    This is for a hospital where the patients take classes and have counselor, coordinators, and an instructor for each class. Would it be easier to have three different tables for coordinator, counselor, and faculty?
    NO, simply because, the way you stated it, all three of those teacher-type people are related to one patient. Hence, they should all be foreign types of data, related right back to the patient table.

    In terms of not being able to query what you asked about, I'm 99% sure that you can it, somehow, someway. It simply may be that you can't query that stuff out with a traditional sql statement. And to tell you the truth, any sophisticated database will probably never run on select statements alone. At least not until we all live under one person on the global scale. And we're not there yet, but I sure give credit to Obama for tryin!

  7. #7
    jice89 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2011
    Posts
    7
    Thanks for your help ajetrumpet, I'll fiddle with it some more for a bit. Wish me luck!

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    OK, this is marked as solved now. If more help needed, please unmark it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 11-30-2010, 11:32 AM
  2. Student Database - fields vary for students.
    By ArmyLT in forum Database Design
    Replies: 6
    Last Post: 11-29-2010, 03:20 PM
  3. Student Information Systems
    By DrCreosote in forum Access
    Replies: 1
    Last Post: 09-29-2010, 06:33 PM
  4. Matching Rows in Table (kinda desperate)!
    By UCBFireCenter in forum Queries
    Replies: 0
    Last Post: 10-15-2009, 04:21 PM
  5. Trying to create Student fixtures database, need help!!
    By jmccullough in forum Programming
    Replies: 0
    Last Post: 08-10-2009, 01:56 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums