Results 1 to 2 of 2
  1. #1
    XenoZephyr is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    16

    Need help with database of college students, how to track a history?

    I am a grad student assistant for a department at my college. I've been asked to convert a very crude and outdated and very poorly designed database (FilemakerPro) to an MS Access database.

    We have the latest version of Access (MS Access 2010), it will be running on a mix of Windows XP (updating to Windows 7 at some point), Windows 7, and a Mac (recent OS, not sure which).

    The database is simple and straightforward for the most part. The database just records each student, standard stuff: name, address, etc, also GRE Scores, previous GPA, etc and we record when they've passed key exams, candidacy, who their advisor is and when they've graduated (or switched programs). Currently their old database is flat, they are just using it as if it was a spreadsheet and it causes lots of issues.

    I've got the basics set up in Access, but I am not sure (though I have some ideas) on how to do the 2 following things:

    We have a Student Table, and a Faculty Table (which just has Faculty Full name as a field right now)

    1)
    We want to have a field that says who the student's current Advisor is AND we want to have a record of any previous Advisors in case they have switched Advisors in the past (and to be able to see when this switch occurred). Besides using this field to look up who someone's advisor is, we also use it to print out lists of Advisees for each of the Faculty Advisors each Fall.

    I think this is the way to approach it, but I am very unsure, any suggestions are Greatly appreciated!

    Make a new Table called Advisements with the following 3 fields
    StudentID (links to StudentID field in Student table)
    Faculty (links to FullName in the Faculty table)
    AssignmentDate (Date when this student was assigned to this Faculty member)

    Then either an autonumber ID field as primary key, or maybe select each of the 3 fields and combine them as one primary key? (not sure about that)

    The idea here is that if someone changes Advisors, then we would add a new entry into the Advisement tables with that StudentID, the new Faculty name, and a new Date. Though I'm not sure how to set that up (adding a new entry instead of just changing the current one).

    Am I on the right track here at all?


    2)
    Very similar to the other problem:
    In our department, students can be in different programs (Mental Health, School Psychology, Counseling Services, Play Therapy, etc). They can also be in more than one program (Many MH students are in one of our certificate programs like Play Therapy for example).

    Right now, the old database is set up like this, it has the following fields:

    Cohort 1 (what year they entered the program)
    Current Program
    Degree
    Decision
    Accepted Cohort 1
    Student Decision


    Graduated
    Graduation Date

    Now, in case there are people in multiple programs, OR if a person switched programs, there are 3 other sets of identical fields (Cohort2, Cohort3, etc). This is an awful setup and we have to do many work arounds (mostly because none of us know anything about FilemakerPro) and it is very complicated for other Grad Assistants who do lack the computer knowledge that I have (and mine is outdated as well).

    The Decision, Accepted Cohort 1, Student Decision, and possibly Graduated are all being combined into a "Status" field instead of how it currently is.

    I believe what I need to do is another multi-relational table.
    The Programs table would have
    StudentID (the link to the Student table)
    Cohort
    Program
    Degree
    Status
    Possibly separate Graduated and Graduation date

    Then a student could have more than one program and if they switch from one program to another one (which happens often) then we could just put in the Status field "Withdrawn" or something like that.

    We want to keep a history, which I believe this will do...

    Is this approach correct?


    Thank you for your time and assistance!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Yes, to maintain historical records will need the additional tables.

    Use form/subform arrangements for data entry.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 5
    Last Post: 01-25-2012, 08:07 AM
  2. Query to keep track of comment history
    By REDandBLACKpack in forum Queries
    Replies: 11
    Last Post: 08-02-2011, 06:45 AM
  3. Students database
    By berni3883 in forum Access
    Replies: 8
    Last Post: 03-29-2011, 03:21 PM
  4. Student Database - fields vary for students.
    By ArmyLT in forum Database Design
    Replies: 6
    Last Post: 11-29-2010, 03:20 PM
  5. New database - Students related DB
    By Hayat in forum Database Design
    Replies: 0
    Last Post: 07-29-2009, 07:59 AM

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