Database Layout Questions

[avagodro]

I have designed a few databases for my work. I have been recently assigned a new one that has me a little stumped. I am looking for some suggestions as to how to go about this.
Essentially, we have a training department that does training in the various aspects of the job. In the various classes, the "students" take a midterm exam to gauge the level of retention of the training midpoint in the class. Then at the end of the class, they need to take a final exam.
My department director would like the "students" to be able to take the exams using Access, since 99% of the questions are either multiple choice, yes/no, true/false, or fill in the blank, and Access would be able to "auto-grade" the tests, essentially.
I am at a loss, basically, as to the layout. The "student" information table would be the easy part. I am unsure of how to create the tables that would have the questions, depending on the class and whether it is a midterm or final, with the answer choices attached.
Any suggestions?

 

[ScottGem]

A questionnaire/survey/test database requires some special structure. If you do a search on one of those keywords you should find some examples.

The basic structure is like this:

tblQuestions
QuestionID (PK Autonumber)
Question

tblRespondents
RespondentID (PK Autonumber)
Respondent

tblResponses
ResponseID (PK Autonumber)
QuestionID (FK)
RespondentID (FK)
Answer

In your situation you would probably want a CourseID FK field in tblQuestions to identify the Course the questions are for. You might also want a AnswerType Field to indicate whether the question is multiple choice, T/F etc. I would probably add

tblAnswers
AnswerID (PK Autoumber)
QuestionID (FK)
Answer

but not link to that table in the FE used for testing to discourage cheating.