Results 1 to 4 of 4
  1. #1
    mjj4golf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Location
    Raleigh North Carolina
    Posts
    9

    Table design to keep track of student accompliishments?

    I have a student database that is to supposedly keep track of one or two activities that they will do every school year. I am trying to figure out a way to do this. Would a recommendation be to use a new table for every school year or would it be better to use one table and add a row for every year with fields for every activity. eg:



    Student Name

    School yr Activity A Activity B Activity C
    2016 ..........x
    2017 ........................x
    2018........................ x............. x

    This is for every students who could be here for ten years

    All recommendations would be appreciated.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Neither. A table with fields for student ID, school year and activity ID. The concept is called normalization:

    Fundamentals of Relational Database Design -- r937.com
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    A normalized table structure:

    tStudent table
    --------------
    StudentID (autonumber)
    FirstN
    LastN
    Addr
    City
    St
    Zip
    phoneH


    tClasses (classes offered, Sci101, Tri202)
    ----------
    classID (auto)
    Subject
    TeacherID
    semester
    Room#
    ClassTime
    meetOn (m,w,f)


    tClassRoster (students in 1 classroom)
    --------------
    RosterID (auto)
    ClassID (long)
    StudentID (long)
    FinalGrade



    tWork (all assigned tests,quizes given to class)
    ----------
    WorkID (auto)
    RosterID
    WorkType (test,lab,quiz)
    GradePts



    tStudentWork (grade student got on test)
    -------------
    StudentWorkID
    StudentID (long)
    WorkID
    Grade

  4. #4
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    The other reply posts are correct. But sometimes we full time database designers can be too correct. If you really only have a few activities - you can get by with a single table:
    Student Name, School yr, Activity A, Activity B, Activity C

    As a single table it is not relational but from this you can query probably everything you would need for/by Student and/or Year. It's just a big list. You could do it in excel though the reporting isn't as good and it isn't a multi user app with interface controls. With just 1 record per Student per Year - you can essentially just copy the prior year record and update it. All very simple. The question in my mind is whether the value for an Activity is just a single value. Is there a progression you wish to track within a year? If so then you end up with multiple records per Student per Year - and so at this point you probably do need to go relational.

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

Similar Threads

  1. Question: Table Design - How to track time from multiple entry?
    By warhead92100 in forum Database Design
    Replies: 5
    Last Post: 08-12-2015, 02:04 AM
  2. ERD of student course database design
    By bazzano in forum Database Design
    Replies: 4
    Last Post: 03-11-2015, 07:31 AM
  3. Student Information System Design
    By ghoughton in forum Database Design
    Replies: 3
    Last Post: 07-16-2014, 01:51 PM
  4. Student Results Database Design
    By wally2k14 in forum Database Design
    Replies: 4
    Last Post: 02-06-2014, 03:00 PM
  5. Table design to Track Multiple Client Types
    By TannerT in forum Database Design
    Replies: 6
    Last Post: 06-02-2010, 08:21 PM

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