Simple Database

Ronniie used Ask the Experts™
My little program is to have 30 multiple choice questions with 5 answers to choose from. Each question is to appear after the selection is made and a cmd button clicked.

I will make the DB in MS Access, should I have 30 tables, that's one for each question or one table containing the whole lot?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Two tables:

(1) Question Master:  Contains an ID field, and the question text.

(2) Answer Master:  Contains an ID field, a QuestionID Field (tied to Question Master ID), the text of the answer, and a flag indicating if this is the correct answer.

Sample data:

1  What is your favorite color
2  When was the war of 1812

ID  QuestionID  AnswerText  Correct
1        1          Red         0
2        1          Blue        1
3        1          Green       0
4        1          Beige       0
5        1          Black       0
6        2          1776        0
7        2          1805        0
8        2          1812        1
9        2          1813        0
10       2          1914        0


The issue is a little bigger than you indicate.

You need to store the 30 questions and 5 multichoice replies, but you also need to store the replies that the user is selecting.

For the questions you can have a single table with either 30 fields (memo type) or 30 records, one per question.

For the replies, you can have a single table with either 5 fields (memo type) or 5 records, one per reply.

The last table would be the replies chosen.  That table should have the ID of the user making the selections and the replies that they selected.

If your program will only be used one time then either of the above will work.  However, if you plan on having numerous 30 question tests, then the 30 fields per record with an ID associated with the test is the best bet. The 5 multichoice would also carry the test ID and the user replies would carry the userid, the test ID and the replies selected.

     TestID     Text   10 char    Unique value PrimaryKey
     Question1  Memo
     Question2  Memo

     TestID     Text    10 char   Unique value Primarykey
     Reply1     Memo
     Reply2     Memo

     AutoID     AutoNumber        Unique value Primarykey
     UserID     Text     10 Char  Duplicate values Index1
     TestID     Text     10 Char  Duplicate Values Index2
     Reply1     Text      1 char   These would be Y/N replies
     Reply2     Text      1 char   These would be Y/N replies

That way you can add new questions, new optional answers and keep the replies for later analysis.

Ask for more help if this isn't clear enough.


the answer choices should be fields in the second table , not records, and the selected answer plus the correct answer should be in 2 fields with a coded value,
ex: choice A,B, and E --> whole answer is ABXXE
choice A and D --> AXXDX

you can easily do this when the button is clicked.

dim answ as string

answ = IIf(me.chkbox1 = true, "A", "X")
answ = answ & IIf(me.chbox2 = true,"B","X")



Very helpful thanks for your efforts.
To use the 'Correct' column:

In your code, when a person selects their answer, you can check the answer's Correct column.  Assuming that the column is numeric, and the selected answer is the current record in the recordset Answers, then the code:

If Answers![Correct] Then
   MsgBox "Correct"
   MsgBox "Wrong"
End If

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial