Test Score Results

[bob.bud]

Hello All,

For reasons that would take too long to explain here, I am planning on having attendees use an Excel form to provide their responses to a test.
The Excel form view is linked to a data worksheet which I would then import to the db I am designing.



Aside from the archaic data capture method, I need advice on a few items...

I am playing with the following tables:

Tbl ans_key
course
1
2
x

course is the key, such that I can determine which set of course answers to compare. 1 through x is determined by the number of questions for the respective course.

Tbl_course_data
course
course_date
instructor
fname
lname
1
2
x

course ties to the answer key table, a few demographic items, and then the responses 1 through x. this data is being imported from the Excel file mentioned.

Tbl_course_results (not sure about this table and maybe this should be a query/report)
course
course_date
fname
lname
1
2
x

the idea was to compare the data imported into tbl_course_data to the tbl_ans_key and store it in the tbl_course_results.
My questions:

1. Is the last table required?
2. What is the easiest way to do the compare of the data imported to the answer key and then display the results?
3. I want to be able to display results for an individual course as well as the aggregate of all courses of the same type

While I would like to use a different data gather approach, the challenge is that not all classes are held in a location where the machines will have Internet access (I know, a shock in this day in age) or, if they do, the attendees may not have an email address that is usable (I know, even crazier). The constraints limit me from using the web as a means to capture the data, and removes the possibility of using an email form to capture the data, so here I am in 2012 and I have to use an Excel file and sneaker net to get the files off the machines and then imported into the db I am designing.


Thanks.

[June7]

You have fields named like Question1, Question2, Answer1, Answer2? This is not normalized structure. Although maybe easier for data entry by students, can often make data processing harder. However, in your case this might be okay. I presume the results of the third table are whether or not the student was correct. Table is not needed. This can be represented in a query with expressions. The query would join the keys table to the answers table on the course ID (jointype - show all records from Answers...). Then an expression can compare the answer key value to the student answer and return 'correct' or 'incorrect'.

IIf([answer1]=[answerkey1],"correct","incorrect")

Ideally, second table should be two tables.

tbl_Courses
courseID
course
course_date
instructor

tbl_StudentAnswers
CourseID
StudentID
1
2
x

Should also have a table of students with unique ID field. Depending on names as unique identifier is risky, what if you have two Jane Smith? Use the ID as primary/foreign keys.

[bob.bud]

June7,

Thank you for your answer, I had put this little project aside and now need to get back at it.

I did not think the third table was necessary, I was thinking about storing the results. I suppose that would just take up space since the query could be run as needed.

With the idea of having multiple tests, I could see answerkey1_1 for the answer for test 1 question 1. However, I was thinking on having the course field determine which answers to compare given that the answer key table with have a corresponding course name and corresponding answerkey1 .... answerkeyx.

How would what I propose change the IIf statement you provided?

Thanks

[June7]

Not much. My example is generic. Don't have a defined data structure to work with. The point is, somehow have to get the student response and the correct response paired up in a query in order to evaluate the response. Otherwise, directly enter the 'correct' or 'incorrect' value into a table. But this does not take advantage of the relational aspects of the data. And if the student's response were misentered and had to be corrected which results in opposite evaluation, the evaluation entry would also have to be corrected. Relational database is supposed to save work, not make it.

[bob.bud]

June 7,

Someday this will get done...


The plan is to import the student responses from an excel file. The course name will be the relationship that drives the comparison. The correct answers will be housed in an answer key table, each of the records in the answer key will hold the correct answers for the respective courses.

I think I have the right approach, just a matter of writing the queries to say compare the student data to the corresponding course in the answer key, and provide results. I would like to get to a point where the report shows individual scores and the aggregate for a particular course on a given day and/or the ytd for the course.

If you can offer up more help, it would be appreciated.

[June7]

This query will compare student answer to the key answer. I show only question 1, you can expand for the others.

SELECT last_name, first_name, Tbl_right_environment_data.course, IIf([Tbl_ans_key].[1]=[Tbl_right_environment_data].[1],"Correct","Incorrect") AS Answer1
FROM Tbl_ans_key RIGHT JOIN Tbl_right_environment_data ON Tbl_ans_key.Course = Tbl_right_environment_data.course;

If you want to be able to add the corrects, then instead of text Correct/Incorrect, use 1/0. Then can add the constructed fields:
Answer1 + Answer2 + ...