Auto-grade tests in Access 2

[platypus71]

I have two tables.
Table1 is tblAnswerKey
It has the fields ClassName, NumQs, A1, A2, A3-A20.
The data in this table is each Class with an online test, the number of questions on that test, and then the approrpiate answer for each question in fields A1-A20 (or however many questions are on the test).

Table2 is tblCourseTest
It has the fields Name, EmailID, TestName, Q1, Q2, Q3-20.
Name is the student name. TestName is the same as ClassName from Table1. Q1-20 is the answer the student selected for their tests.

What I need to do is "grade the tests". I am guessing this needs to be done in a report so I can show what answers are wrong, but I am struggling with how to get the database to show me right versus wrong, as well as give me a Total Number of Correct Answers per Test.

Any help would be appreciated.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

In keeping with PH's posting, there is a normalized sample "At Your Survey"

Duane
Hook'D on Access
MS Access MVP

 

[platypus71]

PHV, I am afraid your reference only shows me a good example of how to design my tables. My tables are already designed. The data in tblCourseTest is imported from an output file on a website that is downloaded regularly, and can not be altered.

I am at the point where I need to figure out how to come up with a grade using the tables that exist. Maybe I missed it in the link you posted, but I don't think it covered the logic in how to grade the tests. I'm uncertain how to even begin the grading process.

 

[PHV]

and can not be altered
So, use it to populate normalised tables.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[platypus71]

I have read and reread your suggestion for normalising these tables and am really confused. How are these tables not normalized?

Table1 has 22 fields. ClassName is the unique field (key) while the NumQs field defines how many questions and then A1...A20 fields contain the answer. This table serves as the master list for all tests and their answers.

Table2 is tblCourseTest
It has the fields Name, EmailID (key), TestName, Q1...Q20.
Name is the student name. TestName is the same as ClassName from Table1, used to identify which test the student took. Q1-20 is the answer the student selected for their tests (questions are multiple choice A-B-C-D-E).

If I am understanding correctly, you are telling me the only way I can grade these tests is to convert these tables to have the following fields:

Table1:
ClassName (or ClassID that then links to another table with the name of the class)
Question
CorrectAnswer

Table2:
StudentID (linking to another table with student info-which is fine and actually already exists but wasn't relevant)
ClassName (or ClassID-see above)
Question
AnswerSelected

Maybe I just don't get it, but I don't see how this solution will better solve my problem of being able to grade these tests within the database. Instead of comparing Q1 to A1 and Q10 to A10, this way I'd have to compare Question=1:AnswerSelected to Question=1:CorrectAnswer.

If you could provide an example of how you propose I obtain a graded score from either of these tables, maybe it will help me understand where I am missing the logic in this.

 

[lespaul]

So, here's what everyone is trying to tell you. Just because you get the data from outside source in this format doesn't mean you have to keep it in a non-normalized format in YOUR database.

What they have been suggesting is that you create a query that normalizes your data so that you can use standard SQL statements to extract the data you need.

SELECT ClassName, 1 As AnswerNumber, A1 As ActualAnswer FROM tblAnswerKey
UNION
SELECT ClassName, 2, A2 FROM tblAnswerKey
...
UNION
SELECT ClassName, 20, A20 FROM tblAnswerKey

SELECT Name, TestName, 1 As QuestionNumber, Q1 As GivenAnswer FROM CourseTest 
UNION
SELECT Name, TestName, 2, Q2 FROM CourseTest 
...
UNION
SELECT Name, TestName, 20, Q20 FROM CourseTest

Now you will have two queries that have a common field (the Question/AnswerNumber field) that you can JOIN together and easily see where the answer given doesn't match the answer provided:

SELECT Q.Name, A.ClassName, QuestionNumber, Q.GivenAnswer, A.ActualAnswer
FROM qryAnswerNormal A
INNER JOIN qryTestNormal Q ON Q.QuestionNumber = A.AnswerNumber

now you should be able to use a query to count where givenanswer<>actualanswer and group that by the person's name. Because the data is now normalized the query to get the counts is very easy. To do it with your existing structure would require a where statement that compared every one of your fields:

WHERE Q1 <> A1 OR Q2 <> A2 OR ....Q20 <> A20

that's why everyone has been talking about normalizing your data...the queries required to extract the needed information are much easier when the data is in the expected (normalized) format.

HTH

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[dhookom]

kudos to Leslie for taking the time to provide a fairly detailed solution with example SQL!

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

(I've been trying to follow this even though it's not mine becasue it seems like a good thing to learn on)

So, instead of each record in the answer table being a student's test, each record is an individual question response, which is tagged with ident info like the test and the student.

Then, because the base unit is the question instead of the test, it becomes easier to manipulate and compare.

So, if there were five 20 question tests and 200 students, the coursetest table would have 20,000 records (assuming each student took each test and answered each question)?

 

[fneily]

And, in my analysis, what LesPaul is doing is fixing(normalizing) your table1 which is not following the first normal form. Granted, this is very difficult for people to see if not experienced. You have repeating column headings: A1, A2, A3, etc. > Drop the number you get A,A,A,etc. This may cause the following:
Students don't give an answer to a question, thus an empty cell will be in a field; what will you do when in the future someone adds 5 more questions? You can't just change the table structure.; What happens when, depending on skill levels, some should answer only 10, others 15, others 20? Bascially, what you have are variable length records which is not allowed in relational databases such as Access.
Same analysis with a question table.
Again, this idea of keeping a table perfectly rectangular is not easy to catch.

 

[lespaul]

BigRed,
Lurking is a completely acceptable thing to do and really helps the learning process! Welcome!

Yes, there would be 20,000 records. Taking our above example, I would structure the data something like this:

[tt]
Student
StudentID (PK)
FirstName
LastName
{any other information about the STUDENT and ONLY the student}

Answers
AnswerID (PK)
QuestionID
ActualAnswer

Tests
TestID (PK)
TestName
{any other information about the TEST and ONLY the test}

TestsTaken
TTID (PK)
StudentID (FK)
TestDate
TestID (FK)

TestAnswers
TAID (PK)
TTID (FK)
QuestionID
GivenAnswer[/tt]

Now, that's just the first pass, I'm sure that there's information that I've missed, but you get the idea. The only information that should be in a table should relate to the KEY of that table. Only information about the student in the student table. Only information about the test in the test table.

Let's say that there are five tests that each student needs to take. Some people would then think..."Oh, I'll put 5 date fields in the student table to show when they took the tests"...except the date the test was taken doesn't have anything to do with the student, it has to do with the date the test was taken and should be in a table dedicated to storing information about tests that have been taken:
who took the test?
what test did they take?
when did they take it?

Like fneily said, what happens when the process is changed and now there are 10 test that everyone has to take. If you had put the testing dates in the student table, you would have to go to that table, add 5 new fields and then update every query in your database to find those five new fields.

With the normalized structure I presented, it's not a problem to add an infinite number of tests to the process and no changes are required to the underlying table structure.

Leslie

 

[platypus71]

Thank you for the help. I understand the logic in why to do this and how to do it. I also understand the flaw in my original thinking.

To answer some questions I saw, the feed I get comes from an external web page. The number of questions shouldn't change, but that was why I had the field "NumQs", to dictate how many A# fields to expect.

As for the breaking down of the tables, like I said, I understand the logic and my flaw, but my thinking was 1 completed test produces the name of the person, their email ID, the test name, and their answers. I was thinking each record should be "per completed test", rather than "per question".

Off to redesign... Thanks for the help.

 

[dhookom]

BigRed1212,
If you want to see how to implement a normalized solution like this, check out the link to At Your Survey in my posting on 24 Mar 08 10:48.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

Duane,

Already downloaded that and been poking around in it.

Grin.