Fred:
I'll make this as simple as I can but as Einstein said about explaining the
Theory of General Relativity, it can be made as simple as possible, but no
more so.
Your problem stems from the fact that the Students and Questions tables are
not normalized. The former, Student Table, should only contain columns
representing attributes of the student, so the two test data columns are
redundant. It should be:
Student Table
….ID, autonumber
….lname, text
….fname, text
In the latter, Questions, the option numbers are four items of data, and
what you are doing by having fours separate columns for these is what's known
as 'encoding data as column headings'. This violates a fundamental principle
of the database relational model, which is that data is stores as values at
column positions in rows in tables and in no other way. Its known as the
'information principle'.
Also you seem to have no relationship between Tests and Questions. I'm
assuming each test relates to a subset of questions, so the Questions table
will need a TestID foreign key column.
So, to cater for both of the above the Questions table needs to be
decomposed into two tables, Questions and Answers and the former needs a
TestID column adding. The tables will thus be:
Questions
….QuestionID, autonumber
….QuestionText, text
….TestID, long integer number
….DateModified, date/time
Answers
….QuestionID, long integer number
….Option, integer number
….Answer, text
….IsCorrect, yes/no (Boolean)
The primary key of the Answers table is a composite one of QuestionID and
Option.
The Results table is now:
Results
….StudentID, long integer number
….QuestionID, long integer
….Option, integer number
….DateAnswered, date/time
Note that this table does not need a TestID column as the TestID value is
determined by the QuestionID value, so to include a TestID column would
introduce redundancy. Similarly the text of the answer is not needed as this
is implied by QuestionID and Option. Nor is a ResultsID primary key column
necessary as StudentID, QuestionID and Option constitute a candidate key and
can thus be the composite foreign key. DateAnsweredshould also be included
in the primary key of a student can take the same test more than once. You
can if you wish keep the ResultsID surrogate key, but if you do so you should
also create a unique index on the three or four column candidate key.
For data entry of the results, you could use an unbound option group
control, but you'll need code to insert data into/read data from the
underlying table. A far simpler approach is to use a combo box set up as
follows:
ControlSource: Option
RowSource: SELECT Option, Answer FROM Answers WHERE QuestionID =
Form!QuestionID ORDER BY Option;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
The combo box's RowSource references the form's QuestionID control, soin
the AfterUpdate event of this the Option combo box should be requeried with:
Me.Option.Requery
Again a combo box or list box would be the simplest means of selecting a
question rather than a tab control, so the above line of code would go inits
AfterUpdate event procedure. The control would be set up similarly to the
Option combo box:
ControlSource: QuestionID
RowSource: SELECT QuestionID, QuestionText FROM Questions WHERE TestID =
Form!cboTest ORDER BY QuestionText;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
And TestID would also be a combo box set up similarly, but in this case
unbound, so with an empty ControlSource property and its Name property would
best not be the column name, so its properties would be:
Name: cboTest
RowSource: SELECT TestID, TestName FROM [Test Master] ORDER BY TestName ;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
The RecordSource property of the form should be query which references the
unbound cboTest combo box:
SELECT Results.* FROM Results INNER JOIN Questions ON Questions.TestID =
Forms![YourFormNameGoesHere]!cboTest;
In the AfterUpdate event procedure of cboTest requery the form and the
QuestionID combo box with:
Me.Requery
Me.QuestionID.Requery
As the DateAnswered value will be the same for all questions answered in a
session, you could do one of two things:
1. Give it a DefaultValue property of Date() so that the current date is
automatically inserted into a new row.
2. Require the data to be entered for the first question answered and carry
it forward automatically to the next row while the form is open, but putting
the following in its AfterUpdate event procedure:
Me.DateAnswered.DefaultValue = """" & DateAnswered & """"
This form would be best in Continuous form view, as each response is a new
row in the underlying Results table, so the unbound TestID combo box and a
bound DateTaken text box would be best situated in the form header.
To count the number of correct answers you can use the DCount function to
count the rows in the Results table, for the current StudentID, cboTest (also
the DateAnswered value if the same test can be taken twice by a student)
where IsCorrect = True. You might need to apply some further arithmetic to
the result depending on how you calculate the 'grading'.
For printing you need to create a query which joins the relevant tables and
restrict the results by referencing the current StudentID and cboTest values
on the form as parameters (also the DateAnswered value if the same test can
be taken twice by a student). The query would then be used as the
RecordSource of a report which can be printed from a button on the form.
Note that its important to ensure that there is no unsaved data in the form
at the time, so the code would be something like this:
' ensure current record is saved
Me.Dirty = False
' print the report
DoCmd.openReport "YourReportNameGoesHere"
Ken Sheridan
Stafford, England
Fred said:
I am working on a testing app. It will have five main tables, 1 for
student names, 3 to hold the test questions and 1 to hold the results.
Student Table
ID, autonumber
lname, text
fname, text
testid of test taken, long integer
DateofTest, date
Test Master
TestID, autonumber
testname, text
DateModified, date
Questions
QuestionID, autonumber
QuestionText, text
Option1, text
Option2, text
Option3, text
Option4, text
CorrrectAns, text
DateModified, date
Results
ResultsID, autonumber
StudentID, long integer
TestID, long integer
QuestionID, long integer
QuestionResponse, text
DateTaken, date
The tests are all multiple choice test that always have 4 possible
reponses.
The main form will be a tabbed form with one tab for each question.
As the student answers by clicking on an option button, I want to
capture the student's response.
After all questions have been answered, then the student will have an
option of grading it and printing the answer sheet.
I can get the questions on to the form with a query that brings in the
student name, test name and the question text, etc.
Since the data coming on to the form is from a query, the big problem
for me.
How do I get the student answers into the results table??
