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.

Hi,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

I haven't create a "survey" database before but there are some examples in the Code Archive that might help you get started.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

sfinlay,

Your answers table should probably consist of:
1. Which question is the answer related to
2. Where it appears in the sort order of all answers for that question, if that's important.
3. The question text ("Describe your stress level:")

It should NOT contain anything about patient responses. That should be in a separate table, which would include fields for:
1. Which patient responded,
2. Which question the response is related to,
3. Which box has been ticked

For each "ticked box", you'd have a record in this patient responses table.

Also, you may want to include a SURVEYS table, rather than assume the database will only ever be for a single survey. Chances are, you'll come out with a revised survey, or an all-new one once this database is up and running. In that case, you'll just need to add a "SurveyID" value to your Questions table, so you know which survey a question applies to.

You may want to check out this quiz maker/taker demo in our Code Archives. A survey and a quiz are very closely related things, I believe, so this should be helpful to you in showing how the tables should be related.

The only thing you'd probably want to ignore is the "Correct answer" bits, since a survey is not about right/wrong answers.

Hope this helps,

Dennis

Apologies thedbguy I could have sworn i'd selected the version. (2010 as it happens)

doctor9 -looking into your quiz db. The relationships should be helpful for my db.
Incidently, I tried to convert your db from 2003 to 2010 but I couldn't. Is that due to some setting in you db ?

Thank you both.

sfinlay,

Having said that, without knowing what sort of problem you had with the conversion, it's hard to guess why it wouldn't convert for you. There's very little unusual about the demo aside from the fact that one form has a subform, which itself has a subform. If you really want to convert it, I'd recommend creating a new, blank 2010 database, and then just import all of the objects from the demo into the blank database.

I've attached an Access 2010 version of the Demo.

Dennis

Hi,


No need to apologize, mine was just a reminder. And for future reference, if you "preview" your post before submitting it, you will have to select the version number again or it will not show up (forum software bug).

Cheers (IMG:style_emoticons/default/cheers.gif)