Lazy Teacher

[Guest]

I created a database for my students to take tests on. I love it, it cuts my grading time down by 75%. What I really want is for Access to grade for me

I have tests each based on a table. tblQuiz8 is my latest. There is an ID field (autonumber), a student ID, Period, and questions 1 - 25. Each record will hold all the answers for each kiddo for each test/quiz. Most answers are multi-coice with answers choices A-F or from drop-menus with answer choices 1-15.

I was playing around with the IIF function, but it is WAY out of my league. How can I get Access (02) to grade for me? I would love any help I can get

Thanks As Always
Rip

 

[Don]

And Next year we cut your Salary, Health Ins., and Get substatute for your
position....

I created a database for my students to take tests on. I love it, it cuts

my grading time down by 75%. What I really want is for Access to grade for
me.

I have tests each based on a table. tblQuiz8 is my latest. There is an ID

field (autonumber), a student ID, Period, and questions 1 - 25. Each record
will hold all the answers for each kiddo for each test/quiz. Most answers
are multi-coice with answers choices A-F or from drop-menus with answer
choices 1-15.

I was playing around with the IIF function, but it is WAY out of my

league. How can I get Access (02) to grade for me? I would love any help I
can get.

 

[John Viescas]

Don-

Those of us who enter the newsgroups to answer questions usually look for
questions that have gone unanswered to try to jump in and help. When we see
a reply, we assume the question has been answered. It's only by luck that I
happened to look at this one. If you're not going to provide an answer to
the question, please refrain from replying.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[John Viescas]

Rip-

This will be difficult to do without redesigning your tables. You have
violated first normal form by listing the questions across. You should also
not build a separate table for each quiz or exam. Your database should look
something like:

tblStudents: StudentID, StudentName, etc.

tblPeriods: PeriodID, Subject, etc.

tblStudentPeriods: StudentID, PeriodID, .. other info related to student
enrolled in that period or class

tblExams: ExamID, ExamTitle, Subject, etc.

tblExamQuestions: ExamID, QuestionID, Question, QuestionType, CorrectAnswer

tblStudentExams: StudentID, PeriodID, ExamID, DateTaken, Grade

tblStudentAnswers: StudentID, PeriodID, ExamID, QuestionID, Answer, Correct
(Yes/No)

It would be a simple matter to match tblExamQuestions with tblStudentAnswers
to "grade" each answer with an Update query. Following that, you could
update tblStudentExams with a percentage correct from tblStudentAnswers or
calculate the value in a query.

But that's probably more work than you want to do. With your current
design, you need to add a "Correct" field (Yes/No data type) to each cluster
of Questions in tblQuizn. You also need to add ExamID to the table. Create
another table that contains all the answers for each quiz (with ExamID as
the Primary Key). Update the "Correct" field with a query something like
this:

UPDATE tblQuiz8Answers INNER JOIN tblQuiz8
ON tblQuiz8Answers.ExamID = tblQuiz8.ExamID
SET tblQuiz8.Q01Correct = (tblQuiz8Answers.Q01Answer = tblQuiz8.Q01Answer),
tblQuiz8.Q02Correct = (tblQuiz8Answers.Q02Answer = tblQuiz8.Q02Answer),
...., [same expression for intervening fields]
tblQuiz8.Q25Correct = (tblQuiz8Answers.Q25Answer = tblQuiz8.Q25Answer)

You'll end up with a True (integer -1) in the Correct field for those that
have answers that match the answer template. You can now add those up and
divide by the number of questions to get the "score" or "grade".

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I created a database for my students to take tests on. I love it, it cuts

my grading time down by 75%. What I really want is for Access to grade for
me.

I have tests each based on a table. tblQuiz8 is my latest. There is an ID

field (autonumber), a student ID, Period, and questions 1 - 25. Each record
will hold all the answers for each kiddo for each test/quiz. Most answers
are multi-coice with answers choices A-F or from drop-menus with answer
choices 1-15.

I was playing around with the IIF function, but it is WAY out of my

league. How can I get Access (02) to grade for me? I would love any help I
can get.

 

[Scott Loupin]

Amen John!

To ultimately set a letter grade, create a Case statement in a module:

Public Function LetterGrade(NumberIn As Integer)

'Assumes a number between 0 and 100

Select Case NumberIn
Case Is <= 60
LetterGrade = "F"
Case Is <= 70
LetterGrade = "D"
Case Is <= 80
LetterGrade = "C"
Case Is <= 90
LetterGrade = "B"
Case Is <= 100
LetterGrade = "A"
End Select

End Function

I also recall seeing a query that had a grade table in it, the grade table
wasn't linked to the student grade, I can't remember how it worked, but will
keep looking.

HTH

Scott

I created a database for my students to take tests on. I love it, it cuts

my grading time down by 75%. What I really want is for Access to grade for
me.

I have tests each based on a table. tblQuiz8 is my latest. There is an ID

field (autonumber), a student ID, Period, and questions 1 - 25. Each record
will hold all the answers for each kiddo for each test/quiz. Most answers
are multi-coice with answers choices A-F or from drop-menus with answer
choices 1-15.

I was playing around with the IIF function, but it is WAY out of my

league. How can I get Access (02) to grade for me? I would love any help I
can get.

 

[John Vinson]

I also recall seeing a query that had a grade table in it, the grade table
wasn't linked to the student grade, I can't remember how it worked, but will
keep looking.

A "Non Equi Join" works for this: a grades table with fields Low,
High, Grade and records like

0; 64; F
65; 78; D
79; 84; C
85; 92; B
92; 99; A
100; 100; A+

(yes, I'm a tough grader... why do you ask?)

A Query like

SELECT * FROM scores, Grades.Grade
INNER JOIN Grades
ON Scores.Score >= Grades.Low
AND Scores.Score <= Grades.High;

will get the same result (with the advantage that the grade
breakpoints are in an editable table rather than buried away in VBA
code).

 

[Scott Loupin]

Ahah - that's the one!

Thanks John!

Scott

A "Non Equi Join" works for this: a grades table with fields Low,
High, Grade and records like

0; 64; F
65; 78; D
79; 84; C
85; 92; B
92; 99; A
100; 100; A+

(yes, I'm a tough grader... why do you ask?)

A Query like

SELECT * FROM scores, Grades.Grade
INNER JOIN Grades
ON Scores.Score >= Grades.Low
AND Scores.Score <= Grades.High;

will get the same result (with the advantage that the grade
breakpoints are in an editable table rather than buried away in VBA
code).

 

[John Viescas]

To do this on the query grid, start a new query on your new tblQuiz8Answers
table. Add the tblQuiz8 table - Access should guess and draw a linking line
from ExamID in the first table to ExamID in the second. Choose Update Query
from the Query menu. Drag each of the 25 QxxCorrect fields from tblQuiz8
onto the query grid. In Update To under Q01Correct, enter:

(tblQuiz8Answers.Q01Answer = tblQuiz8.Q01Answer)

Do the same for the remaining 24 question and then run the query. It should
update all the "correct" columns in the answer rows in tblQuiz8.

Next create a query on tblQuiz8. Drag the StudentID onto the grid. In an
empty Field, enter:

PercentRight: Abs(Q01Correct + Q02Correct + Q03Correct + Q04Correct +
Q05Correct + Q06Correct + Q07Correct + Q08Correct + Q09Correct + Q10Correct
+ Q11Correct + Q12Correct + Q13Correct + Q14Correct + Q15Correct +
Q16Correct + Q17Correct + Q18Correct + Q19Correct + Q20Correct + Q21Correct
+ Q22Correct + Q23Correct + Q24Correct + Q25Correct) /25

Run the query to see the percent correct for each student.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

OK, the relationships alone will bury me in a few hours, but I'm going to

try it your way. You sound like a knowledgeable fellow =)

The only trouble I have understanding your reply was about the update

query. How, in the design view of a query, do I place that information?
(BELOW)

RIP

UPDATE tblQuiz8Answers INNER JOIN tblQuiz8
ON tblQuiz8Answers.ExamID = tblQuiz8.ExamID
SET tblQuiz8.Q01Correct = (tblQuiz8Answers.Q01Answer = tblQuiz8.Q01Answer),
tblQuiz8.Q02Correct = (tblQuiz8Answers.Q02Answer = tblQuiz8.Q02Answer),
...., [same expression for intervening fields]
tblQuiz8.Q25Correct = (tblQuiz8Answers.Q25Answer = tblQuiz8.Q25Answer)

 

[Guest]

I got an error message when I tried to use the (tblQuiz8Answers.Q01Answer = tblQuiz8.Q01Answer

I guessed, and was right for once, that you had to enclose the formula with [
([tblQuiz8Answers.Q01Answer] = [tblQuiz8.Q01Answer]

Same with the PercentRight: all the QnnCorrect must be in brackets as well [QnnCorrect

Thank you so much you saved me a ton of time in grading. Of course a teaching theorm goes
Easy test to make / Long Test to Grade. Long test to make / Easy test to grade

Ri

----- John Viescas wrote: ----

To do this on the query grid, start a new query on your new tblQuiz8Answer
table. Add the tblQuiz8 table - Access should guess and draw a linking lin
from ExamID in the first table to ExamID in the second. Choose Update Quer
from the Query menu. Drag each of the 25 QxxCorrect fields from tblQuiz
onto the query grid. In Update To under Q01Correct, enter

(tblQuiz8Answers.Q01Answer = tblQuiz8.Q01Answer

Do the same for the remaining 24 question and then run the query. It shoul
update all the "correct" columns in the answer rows in tblQuiz8

Next create a query on tblQuiz8. Drag the StudentID onto the grid. In a
empty Field, enter

PercentRight: Abs(Q01Correct + Q02Correct + Q03Correct + Q04Correct
Q05Correct + Q06Correct + Q07Correct + Q08Correct + Q09Correct + Q10Correc
+ Q11Correct + Q12Correct + Q13Correct + Q14Correct + Q15Correct
Q16Correct + Q17Correct + Q18Correct + Q19Correct + Q20Correct + Q21Correc
+ Q22Correct + Q23Correct + Q24Correct + Q25Correct) /2

Run the query to see the percent correct for each student

--
John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993

OK, the relationships alone will bury me in a few hours, but I'm going t

try it your way. You sound like a knowledgeable fellow =query. How, in the design view of a query, do I place that information
(BELOW

RI
UPDATE tblQuiz8Answers INNER JOIN tblQuiz

ON tblQuiz8Answers.ExamID = tblQuiz8.ExamI
SET tblQuiz8.Q01Correct = (tblQuiz8Answers.Q01Answer tblQuiz8.Q01Answer)
tblQuiz8.Q02Correct = (tblQuiz8Answers.Q02Answer = tblQuiz8.Q02Answer)
...., [same expression for intervening fields
tblQuiz8.Q25Correct = (tblQuiz8Answers.Q25Answer = tblQuiz8.Q25Answer

 

[John Viescas]

Damn "air code!" Actually, this should also work:

([tblQuiz8Answers].[Q01Answer] = [tblQuiz8].[Q01Answer])

References to table name and field names on the right side of an assignment
in an Update query should be in brackets so that JET knows these are
table/field references and not literals.

Glad I could help!

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I got an error message when I tried to use the (tblQuiz8Answers.Q01Answer = tblQuiz8.Q01Answer)

I guessed, and was right for once, that you had to enclose the formula with [ ]
([tblQuiz8Answers.Q01Answer] = [tblQuiz8.Q01Answer])

Same with the PercentRight: all the QnnCorrect must be in brackets as well [QnnCorrect]

Thank you so much you saved me a ton of time in grading. Of course a teaching theorm goes:
Easy test to make / Long Test to Grade. Long test to make / Easy test to grade.

Rip

----- John Viescas wrote: -----

To do this on the query grid, start a new query on your new tblQuiz8Answers
table. Add the tblQuiz8 table - Access should guess and draw a linking line
from ExamID in the first table to ExamID in the second. Choose Update Query
from the Query menu. Drag each of the 25 QxxCorrect fields from tblQuiz8
onto the query grid. In Update To under Q01Correct, enter:

(tblQuiz8Answers.Q01Answer = tblQuiz8.Q01Answer)

Do the same for the remaining 24 question and then run the query. It should
update all the "correct" columns in the answer rows in tblQuiz8.

Next create a query on tblQuiz8. Drag the StudentID onto the grid. In an
empty Field, enter:

PercentRight: Abs(Q01Correct + Q02Correct + Q03Correct + Q04Correct +
Q05Correct + Q06Correct + Q07Correct + Q08Correct + Q09Correct + Q10Correct
+ Q11Correct + Q12Correct + Q13Correct + Q14Correct + Q15Correct +
Q16Correct + Q17Correct + Q18Correct + Q19Correct + Q20Correct + Q21Correct
+ Q22Correct + Q23Correct + Q24Correct + Q25Correct) /25

Run the query to see the percent correct for each student.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

OK, the relationships alone will bury me in a few hours, but I'm

going to
try it your way. You sound like a knowledgeable fellow =) update
query. How, in the design view of a query, do I place that information?

(BELOW)

RIP
UPDATE tblQuiz8Answers INNER JOIN tblQuiz8

ON tblQuiz8Answers.ExamID = tblQuiz8.ExamID
SET tblQuiz8.Q01Correct = (tblQuiz8Answers.Q01Answer = tblQuiz8.Q01Answer),
tblQuiz8.Q02Correct = (tblQuiz8Answers.Q02Answer = tblQuiz8.Q02Answer),
...., [same expression for intervening fields]
tblQuiz8.Q25Correct = (tblQuiz8Answers.Q25Answer = tblQuiz8.Q25Answer)

 

[coolBreeze]

you must be a republican!

-----Original Message-----
And Next year we cut your Salary, Health Ins., and Get substatute for your
position....


I love it, it cuts
my grading time down by 75%. What I really want is for Access to grade for
me. latest. There is an ID
field (autonumber), a student ID, Period, and questions 1 - 25. Each record
will hold all the answers for each kiddo for each test/quiz. Most answers
are multi-coice with answers choices A-F or from drop- menus with answer
choices 1-15. WAY out of my
league. How can I get Access (02) to grade for me? I would love any help I
can get.


.