Results 1 to 4 of 4
  1. #1
    ArmyLT is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    7

    Storing, updating, displaying historical test scores for students


    All,

    I'm OK with the basics, but am a bit out of my realm on this one...

    I'd like to store and report on historical test scores for students in a military environment.

    Student James is in Class 2-11 and scores 60 in bridging and fails. His retest is 65 and fails. He recycles to Class 3-11 and scores 69 and fails. His retest in Class 3-11 is 99 and he passes.

    I'm struggling with a few things... First, is the data entry screen for Exam "X." I have two tables (Students (all students from all classes) and Exam Scores(all scores from all classes on all exams)) and I've built a Left Join query that finds all the test scores for those in the class who've taken this particular test (from ExamScores) and those other in the class who haven't taken the test (from Students based on ClassID).

    Name Class # Test Score Date Class (when test was taken)
    James 3-11 Bridge 60 1-1-11 2-11
    James 3-11 Bridge 65 1-5-11 2-11
    James 3-11 Bridge 69 2-1-11 3-11
    Smith 3-11 Bridge 80 2-1-11 3-11
    Jones
    Case
    Hart
    Keith

    Question 1:
    I'm shooting for a data entry screen that shows only the students for a specific class (3-11) who need a score entered (Those who haven't taken the test, or those who haven't passed yet). Then again, I'd be happy with an entry form that has all students in the class and their highest passing score. Not tied to any approach...

    I've got a frmExamEntryDialog that has a Class combo-box (3-11), an Exam combo-box (Bridging), and Date of the Exam that then fires off a data entry screen with a datasource that is a temp table based on the Left Join shown above.

    I'm wanting to see (from the above example) -
    Name Test Score
    James ____________
    Jones ____________
    Case ____________
    Hart ____________
    Keith ____________
    (I don't want to see all of James failures, or Smith's passing score - just those who haven't passed yet, or haven't taken the test)

    What also would be cool is that the Test Score textbox would highlight if there was previous failure and might even have the option to pop up all this student's testing history of this particular exam.

    Additionally, if a student has failed an exam, the highest score they can get on a retest is 70%.

    Hopefully, I can leverage what I learn from this to create other functionality (Average of a student's test score, average of the class, average on an exam, student academic report showing all test history, etc).

    Meh - hope that makes sense....

    Thanks -

    - John

    * Edit 1: By the way, the frmExamScoreEntry Form is bound to the Temp Table created by the Left Join. From my understanding there are issues binding a continuous form to a recordset. Again, happy to change approaches if I'm going about any of this wrong.

  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
    I recommend you do some research on Normalization and get your tables structured.

    Once you get the tables normalized, you'll find that queries, reports etc are rather straightforward.

    There are excellent references in the second posting at this site
    http://www.utteraccess.com/forum/Sum...-t1960935.html

    see the list of links provided by BananaRepublic.

  3. #3
    ArmyLT is offline Novice
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    7
    Orange,

    Thanks for the response. While I didn't specifically breakdown my table structure, my data seems to be fairly well normalized.

    Student Table
    StudentID
    ClassID
    FName
    LName
    etc...

    Class Table
    ClassID
    ClassNumber
    ClassCadre
    etc...

    Exam Table
    ExamID
    ExamName

    Exam Scores Table
    ExamScoreID
    ExamID
    StudentID
    ExamScore
    ExamDate

    I understand normalization and feel pretty confident about my tables and relationships. And I can query the info with no problem with me at the controls. My issue is trying to develop a form that an average user can work with. That is displaying a form that allows entry of scores for those students in this class who haven't taken or passed this particular exam yet. One student can have multiple exam scores for the same exam and still not pass.


    How 'bout some pseudo code?

    After a exam, I want the Cadre to be able to enter exam scores.

    Pull a roster of all the students of this class (say Class 3-11), regardless if they have any test scores for this particular exam or not.

    If any of the 3.11 Student's Bridging ExamScores > Passing
    Exit
    Else
    Display a frmScoreEntry (in a continuous form)
    End If

    The problem is, some student in the class may have a passing grade, somebody else in the class may have no grade, and somebody else may have several failing grades.

    I've tried to go straight queries and temp tables, but I don't think I can get there from here. I may have to jump into the world of recordsets and analyze each students record.

    - Build recordset (All 3-11 students and any Bridging Exam Scores)
    - Group them based on StudentID
    - Analyze StudentID 1.
    - If he needs an exam entry, add him to a temp table.
    - Bind the ExamScore form to the temp table.

    Again, don't need a line by line of code - just what approach to use....

    Thanks again,

    - John

  4. #4
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    John,
    Haven't really analyzed your situation but I would consider using a filter in VBA. Below is the general syntax for the filter. This should filter out everyone who has a passing score.

    Code:
    VariableName = (Field with passing "flag" in it)
    Me.Filter = "[TableFieldName] = """ & VariableName & """"
    Me.FilterOn = True
    You'll need to have a text box hidden with the value you want to filter on if you don't have it already on the form. You can hide this text box so the general user doesn't see it.

    Keep in mind this filter syntax is for a string variable. You'll need to alter the syntax if you want to filter on number/date, etc.

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

Similar Threads

  1. Replies: 1
    Last Post: 12-09-2010, 08:29 AM
  2. Query using historical table
    By kwilliams5675 in forum Queries
    Replies: 1
    Last Post: 10-05-2010, 03:23 PM
  3. Recording Test Scores and Radar Charts
    By splitz in forum Access
    Replies: 1
    Last Post: 08-19-2010, 12:34 PM
  4. Keying in Test answers to Access DB from Written Test
    By CityOfKalamazoo in forum Access
    Replies: 3
    Last Post: 03-01-2010, 08:58 AM
  5. Students
    By Rohit0012 in forum Reports
    Replies: 8
    Last Post: 10-27-2009, 04:04 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