Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    cohnhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    20

    Student Pre-Test and Post Test Skills Tracking Question

    Hi,

    I am not new to Access, but by no means an expert. I am a middle school teacher and I am trying to design a database that will keep track of student pre-test and post-test skills data.

    Here is an example of what I am tracking:
    Student A takes a pre-test in August (I don't need the score) which measures mastery of a group of skills.
    Compare and Contrast - mastered
    Venn Diagrams - mastered
    Analogies - not mastered
    Research - not mastered
    Prefixes - not tested

    Student A takes a post-test in October (no score needed) which measures mastery on the same set of skills
    Compare and Contrast - mastered
    Venn Diagrams - mastered
    Analogies - mastered
    Research - still not mastered
    Prefixes - not tested



    I want to be able to enter this information in the database based on the test results. I would also like to be able to see the improvement in a report. Any help would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Your options are:

    1. one big flat file table - this is one record for each student with a field for each before test and each after test

    2. normalized data structure with at least two tables - one for basic student info and one for the test results - results table would have a record for each student for each test with fields for before and after results
    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.

  3. #3
    cohnhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    20
    Quote Originally Posted by June7 View Post
    Your options are:

    1. one big flat file table - this is one record for each student with a field for each before test and each after test

    2. normalized data structure with at least two tables - one for basic student info and one for the test results - results table would have a record for each student for each test with fields for before and after results
    Before I get into some deeper questions in regards to option 2, how do I set up the skills fields? I want to be able to check each skilled as either "mastered" or "not mastered" on the pre-test and again on the post test. I have about 30-40 skills that would need to checked.

    I think the flat file table would get too tedious because of the number of skills and having to input them twice. So I could probably try option 2. But I am unsure how to set up the fields for tracking the skills.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    And now I see 3 tables. The test results table would have maybe 6 fields:

    tblTestResults
    StudentID
    SkillID
    DateBefore
    ResultBefore
    DateAfter
    ResultAfter

    tblStudents
    StudentID
    LastName
    FirstName

    tblSkills
    SkillID
    Description
    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.

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    June's given you one default for how these things should be set up. Definitely don't have them on a flat table, or you'll have nightmares trying to get decent reporting out of it.

    However, I'd say that if you plan to do multiple tests, and there will not always be exactly two tests - like, will you ever allow makeups in November? - then you should break it down like this -
    Code:
    tblTestResults
     StudentID
     SkillID
    TestDate
    TestResult
    You will then be able to get reporting of which students have a LATEST test where they failed to master the skill. There are also various crosstab reports that will give you lots of useful data. If you put the two tests in the same record, then that option will not be available.

    Input screen design is a deep subject, but here's a good way to think about it - the computer can do the repetitive stuff for you, if you take the time to allow it. You could do your entry by student or by test. You can set up a screen where you select all the students that passed a particular test... or a screen where you can select all the tests that a particular student passed and/or failed.

  6. #6
    cohnhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    20
    Thank you! Those both help. I have set it up based on June's layout because I am only trying to track overall progress from the pre-test to the post test. I will have tests in between, but do not need to track those results. But I do have a couple of questions.

    I set up 3 tables like this:

    tblTestResults
    StudentID - Primary Key
    SkillID
    DateBefore
    ResultBefore
    DateAfter
    ResultAfter

    tblStudents
    StudentID -Primary Key
    LastName
    FirstName

    tblSkills
    SkillID - Primary Key
    Description

    I also created a relationship from
    StudentID (tblStudents) to StudentID (tbltestresults)
    and SkillID (tblSkills) to SkillID (
    tbltestresults)

    If my questions are too newbie, please let me know. Is this the correct setup? Are my primary keys ok?

    Also, after I get the tables set up, I want to be able to enter multiple skills for each student. So in my tblSkills I have listed numerous skills. In my tblTestResults, the ResultBefore and ResultAfter fields are lookup fields with 3 choices (Mastered, NotMastered, and Not Tested).

    For example: Student A took the pretest and mastered skill 2,6,7,8, and 9. But he did not master 1,3,4, and 5. I want to be able to enter that into the results.

    June and Dal, I appreciate all your help.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    StudentID in tblTestResults would be a foreign key as will be SkillID. Could give them different names so as to distinguish from the primary key fields of other tables. Could set them as compound index to prevent duplicate pairs.

    Building lookups with alias in table is hazardous to mental health unless you understand the value displayed is not the actual value saved http://access.mvps.org/access/lookupfields.htm. Use a combobox on form to select skill.
    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.

  8. #8
    cohnhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    20
    Quote Originally Posted by June7 View Post
    StudentID in tblTestResults would be a foreign key as will be SkillID. Could give them different names so as to distinguish from the primary key fields of other tables. Could set them as compound index to prevent duplicate pairs.

    Building lookups with alias in table is hazardous to mental health unless you understand the value displayed is not the actual value saved http://access.mvps.org/access/lookupfields.htm. Use a combobox on form to select skill.

    Ok. I am getting close. I updated the keys like you said and it is working. I created a form with combo boxes for skills and students. However, I am only able to do one skill at a time on the form per student. Is there a way to select the student on the form and then select all of the skills mastered and not mastered on the pre-test and/or post-test?

    Ideally, I would like all of the skills to be listed at once on the form and I just check whether they are "mastered", or "not mastered."

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    This would require code that 'batch' creates set of skill records for each student. That code would run an INSERT SELECT sql action statement to create the records.

    CurrentDb.Execute "INSERT INTO tblTestResults(StudentID, SkillID) SELECT StudentID, SkillID FROM tblStudents, tblSkills"

    That will create set of records for every student. Set the StudentID and SkillID fields in tblTestResults as compound unique index and will prevent duplication of pairs.

    Now have to open form displaying these records for data entry of other info.

    DoCmd.OpenForm "form name", , , "DateBefore Is Null OR DateAfter Is Null"
    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.

  10. #10
    cohnhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    20
    Quote Originally Posted by June7 View Post
    This would require code that 'batch' creates set of skill records for each student. That code would run an INSERT SELECT sql action statement to create the records.

    CurrentDb.Execute "INSERT INTO tblTestResults(StudentID, SkillID) SELECT StudentID, SkillID FROM tblStudents, tblSkills"

    That will create set of records for every student. Set the StudentID and SkillID fields in tblTestResults as compound unique index and will prevent duplication of pairs.

    Now have to open form displaying these records for data entry of other info.

    DoCmd.OpenForm "form name", , , "DateBefore Is Null OR DateAfter Is Null"

    Thanks June! Worked Perfectly.

    I created the append table like you said and that added everything I needed. I also was able to create a form from tblStudents which allowed me to do all the skills for one student at a time.

    This is not a big deal, but since I am perfecting this database, I thought I would ask. When checking the skills for each student, only the skillID shows up on the form. Is there any way to make the skill description show as well on the form?

    Your help has really made this database work perfectly!!

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Show the skill description by including the skills table in the form RecordSource. The RecordSource can be reference to a query object that joins the tables or can be SQL statement.

    Bind textbox to the description field and set properties Locked Yes, TabStop No.
    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.

  12. #12
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I would suggest that you NOT make the studentID the primary key of tblTestResults. Give that table its own autokey field as primary key, and have StudentID as a foreign key to the student table. (JUne said that, but didn't emphasize it enough.)

    That will save you a world of hurt later.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Thanks Dal, did not notice that.

    tblTestResults doesn't really need a primary key. If the need does arise, it can be added later.

    Can set StudentID and SkillID as compound unique index to prevent duplicate pairs.
    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.

  14. #14
    cohnhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    20
    I am stuck on that last part. I added a primary key for tblTestResults. I can remove it if not needed. But I can't figure out how to set up the form so I can do all the skills for each student on one page. I am attaching what I have created so far. Any suggestions?
    Attached Files Attached Files

  15. #15
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I tend to give all permanent tables a primary autokey, because I find it allows me to vastly simplify my SQL in some complex joins.

    Also, when I'm doing cleanup, it allows me to create a temporary table of the keys of the records I plan to kill, and verify that I'm killing the right ones.

    It's just a preference, and, like you say, it could be added on the fly if necessary.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Test String test besed on table data
    By igourine in forum Programming
    Replies: 3
    Last Post: 12-01-2013, 06:16 AM
  2. Lab Test and Parameters ERD
    By cap.zadi in forum Database Design
    Replies: 3
    Last Post: 11-14-2012, 09:29 AM
  3. Database for test progress tracking
    By Ricardo in forum Database Design
    Replies: 3
    Last Post: 06-07-2012, 07:20 AM
  4. Test
    By goatamus in forum Access
    Replies: 1
    Last Post: 02-02-2012, 02:44 PM
  5. 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

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