Hi again.

I need some help with the following. I'm trying to design a small database which is essentially a patient survey consisting of 19 questions, each with a simple A, B or C answer.

This is what I have so far :

3 Tables

A Patients Table with the fields

PatientID (An Auto Number)
First Name
Last Name
Todays date

A Questions Table with the fields

Question ID (Numbers 1-19)
Question Text (The actual content of the question)

An Answers Table with the fields

Question ID (ie linking to the question table)
Answer A  
Answer B
Answer C (each being a yes/no data type to show which box has been ticked)

I'm not sure if  I'm going about this in the right way, for example each patient might take the same survey more than once (One to many relationship) but I can't see how to link this to the actual questions and answers ?
Also, depending on the patient answer, each answer has a relative "points" value. For example an A answer = 0, A B answer= 1 and a C answer = 2 points. The idea is to add all the answered questions to get a total score, again, I can see how to achieve this ?

Any help very much appreciated.

---

Reply from Richard Rost:

I would do this:

PatientT: PatientID, FirstName, LastName, etc.
PatientTestT: PatientTestID (AutoNum), PatientID (Foreign), TestDate, TotalScore, etc.

This allows you to track multiple tests for each patient. Now if you just care about saving the total score from their test, you can save that in the PatientTestT. If you need to track ALL of their answers, you'd need a third table:

TestAnswerT: TestAnswerID (AutoNum), PatientTestID (Foreign), QuestionID, AnswerGiven

Now for your points, just add that to your Answer table:

AnswerT: AnswerID (AutoNum), AnswerText, PointsGiven (Int)

Now for each answer, you can just add up the points in a form field or global variable (or even drop it in the PatientTestT table).