comparing fields from 2 tables and then counting the results

[Bruce Musgrove]

I am trying to create a form to allow me to enter a students test answer's,
compare them against the correct answer in Table2 and then display the
score.
Table 1 is the student, testName and one field for each question (named 1, 2
, 3 etc)
Table 2 is the TestName and 1 field for each question (named 1, 2, 3 etc)
Right now there are only 10 answer fields but when we are done there will be
70-150

I can do this individually on each field by comparing the fields for each
question, set the value of a hidden field then have the score fieldadd up
all of the hidden score fields. This however will be a pain for a 150
fields. My intent would be to have the score field do the comparisons and
create the score, but again, writing the code to compare each and every
answer field independentlyand then scoring it would a royal pain.

Is there an easier way to do this using an array or looping the comparisons?
IE making it do a for...each statement to loop through all questions and
provide a score/count to me? Declare two arrays and then compare them for
field entries that are equal? Another method to keep from hand coding each
field comparison?

 

[Michel Walsh]

Hi,


Nope. Try the following design:

StudentID, QuestionID, Answer ' Fields name


ie, one record per question. You would always have 3 fields, whatever the
number of questions is 10 or 999.



SELECT a.StudentID, COUNT(*)

FROM table1 As a INNER JOIN table2
ON ( table1.QuestionID=table2.QuestionID )
AND
( table1.Answer = Table2.RightAnswerIs )

GROUP BY a.StudentID


should return, per student, the number of good answers... quite simply, no
loop. You can even do it graphically, in the query designer: bring the two
tables, join them through their QUestionID fields, and again through their
Answer fields. Click on the summation button on the toolbar, a new line,
total, appear in the grid. Drag StudentID from the first field in the grid,
keep the proposed GroupBy. Drag it again, this time, change the Group By to
COUNT. That's all, job done... when you free yourself of the chains that
impose on you the "recordset" approach, job is that easy... with a good
(normalized) table design.



Hoping it may help,
Vanderghast, Access MVP

 

[Bruce Musgrove]

In other words if I have a 10 question test and a 30 question test, my test
answer key table would have 3 fields (testID, QuestionID, Answer Field),
yielding a total of 40 records. Obviously this would increase as I added
more tests.

The student answer table would have StudentID, TestID , QuestionID and
Students Answer (4 fields) yielding 40 records per student

The student answer table would

Aftre that it is essentialy a query joining the two by test ID and Question
ID and comparing the answer fields.

Makes sense and is blindingly obvious and simple. I was trying to be way to
fancy. Thank you for waking me up!