Questionairre database

[balllian]

How best is it to design a questionairre database. I understand i need to have a table for the questions and also the answers and then also to have a table to store the details of the people who are going to complete the database.

What is the best structure to create this from outset regrads the relationship and then to record the answers onto another table.

Any thoughts on how best to construct this as i not too sure what is best...

thanks in advance

 

[lespaul]

check out Duane's sample survey database. There's a link in Thread181-1264416

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[balllian]

i have looked at this and this is a good example to follow. One of the issues i have is as this needs to capture for example name, address etc for each person completing the questions is how to design the database so these get captured on the results table on completion of the questions. what is the best way to structure this relationship.

 

[lespaul]

I might add a person table with PK of PersonID and add that as a foreign key to a table that stores which survey the person took. I might add questions to the survey that ask Name, Address, etc and save that information with the other survey responses.

I guess it would depend on if the same person can take multiple surveys.

I don't know what you mean by

so these get captured on the results table on completion of the questions.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[balllian]

My initial thoughts over this was to create a 3 tables.

1. user details.
2. questions
3. answers to the questions

this would be presented onto a form on which the one survey this database is for would then capture the results onto another table and this would be the 4th table

4. responces

i was curious how this can be put together on the relationship side. This survey/questionairre can only be taken once by one person.

 

[dhookom]

I would add a user table with a primary key of UserID. Then add this field as a foreign key to tblSrvRspns. You can then use a combo box on frmSurveyResponses to select the UserID.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[balllian]

On doing this. what is the best way to get the information captured after answering the 50 questions into another table after completing the survey?

 

[dhookom]

I don't understand your question. "get the information captured" doesn't mean much to me.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[balllian]

as the answers to my questions are all true or false i want to capture these into another table so i can have all the answers in one location for querying.

I was wondering what is the best way to capture this information into a new table as i have already set-up up a table with all the potential answers already and wasnt sure what the best way was to proceed.

Sorry for any mis-confusion on my last post.

 

[dhookom]

If you have entered responses to questions, then all of the answers are already located in one location (assuming location means table).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[balllian]

i have a table set up for all the responces to the questions but as different people will be using the questionairre i want the answers if possible to be placed into another table recorded with the who the person who has completed the questionaiire.

what is the best way to do this?

 

[dhookom]

Sorry, I still don't understand what you have or where you want to go.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[balllian]

i have a table set-up with pre-determined answers already. ie yes/no. once an individual completes the questionairre i want to store the answers i obtain in another table. This form is going to be used to collate information from numerous sources so once the questionaiire has been completed by one individual it can then be used by the next person.

i am curious how i can move the answers i have into another table while recording the id of the person who has completed the questionairre.

how best is it to associate my user table into my existing set-up. i have the following table

tbl - Questions
Tbl - Responce_list
tbl - Question Area

i have also set the User table.

 

[lespaul]

Ok can you give us field names, sample data and exactly what you are unable to do....


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[balllian]

I have the following tables

Question area, Questions & Responces_list

These are interlinked and the responces are recorded as Yes or no.

I have got all the questions & responces to appear in a form sucessfully.

The first issue i am having how can i link the usertable into the either of the above three tables so when the user completes the 49 questions in my table that the answers given get associated with this individual user. I was hoping to save the completed questionaire data on another table so this in principl would record the 'Responce_ListID' alongside the User ID found in the 'User Table'.

How would it best to associate the User ID onto the 'responce_capture' table when saving the data onto this table.

 

[dhookom]

Ballian,
Did you read Leslie's response "Ok can you give us field names, sample data and exactly what you are unable to do...."?

Does your table structure look anything like the "At Your Survey" demo?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[balllian]

here is the stucture of the database

Question tbl

QuestionID -PK
Areaid -FK
Question -datafield

Area Tbl

Area ID -PK
Question Area - datafield

Responces list

QuestionID - FK
Responce - Yes/No
Comments - Datafield

Responce_capture*

Question_ID
Name_ID
Responce -Yes/No

*This table would capture the data after the questionaire has been completed. I want to record the reponces from what is 'responce_list' to be captured in 'Responce_capture' with details recorded from this table.

I want the questions to appear on a form and once this has been completed the responces to be saved and also the nameID to be populated against all 48 questions.

User Tbl

NameID - PK
Name of Business - Datafield

i hope this is somewhat easier to understand.

 

[dhookom]

Are you stating that when entering results, you are not entering them directly into the Responce_capture table?

Did you review the At Your Survey demo to see how responses to questions are stored for different survey and individuals?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[balllian]

As the Answers are all 'yes or no' i have setup a table 'Responce_list' currently the 'responce_capture' doesnt contain any data, My initial thoughts were to capture the answers in this table along with the User ID.

I have looked at the database but im somewhat confused in where best to link the user_id into the current tables to capture each responce from my questionaiire.

 

[dhookom]

You should be able to create an append query that appends from Responce_List into Responce_Capture. Include some column from the User table. I don't know which field since you mentioned "User ID" but the field name might actually be NameID.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]