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.