Build A Survey Database In Access

A survey database has a very specific structure and you are not using it. Using fields to stand for each question is not correct or normalized.

The proper structure is:

tblQuestions

QuestionID (Primary Key Autonumber)

Question

GroupID (Foreign Key)

tblRespondent

RespondentID (PK Autonumber)

FiratName

LastName

Other info about respondent (your patient)

tblResponse

ResponseID (PK Autonumber)

RespondentID (FK)

ResponseDate

tblAnswers

AnswerID (PK Autonumber)

ResponseID (FK)

QuestionID (FK)

Answer

The general data entry structure is a main form bound to tblResponse where the user selects the respondent etc. and a subform with a record for each question and its answer.

I don't believe Ken does contract work anymore, but I do. You can contact me through my website (listed in my profile) or my blog (listed below). 

Or you can just ask questions in this forum. If you do, please start a new question to do so. Use the Participate link above to start a new question.

The best way to achieve a form layout like that would be to have a parent form, in single form view, based on a table structured along these lines:

PatientSurveys

....Pat_ID  (FK)

....RoundNumber

....SurveyDt

....EnteredBy

....DateEntered

And a  subform, in continuous forms view, based on a table structured along these lines:

PatientSurveyResponses

....Pat_ID  (FK)

....RoundNumber (FK)

....SurveyID (FK)

....QuestionID  (FK)

....Answer

The primary key of this table would be a composite one of the first four columns.  Of these Pat_ID and RoundNumber would be a composite foreign referencing what you say would be the composite primary key of PatientSurveys, and SurveyID and QuestionID would be a composite foreign key referencing the composite primary key of the following table:

SurveyQuestions

....SurveyID (FK)

....QuestionID  (FK)

The other referenced tables in the model would be Surveys, Questions and Patients.

When a row is inserted into PatientSurveys in the parent form rows would automatically be inserted into PatientSurveyResponses by executing an 'append' query in the parent form's AfterInsert event procedure, followed by requerying the subform to show the newly inserted rows ready for the answers to be entered.

What you don't do is have separate tables to represent the three surveys, or encode data as column headings by having columns Q1, Q2, Q3 etc.  As a design for a relational database that would be about as bad as it could get.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1).  This requires that all data be stored as values at column positions in rows in tables, and in no other way.  A table structured in the way you describe would really be no more than a spreadsheet masquerading as a relational database table.  A correctly designed relational database table on the other hand models an entity type, with each column representing an attribute of the entity type.

To achieve the output in a horizontal format with columns per question the best way to do this would be as a report based on a query joining PatientSurveys and PatientSurveyResponses, which returns distinct rows of all columns from PatientSurveys and SurveyID from PatientSurveyResponses.  Within this embed a multi-column subreport based on PatientSurveyResponses with across-then-down column layout.  Group the parent report by SurveyID and put the subreport in the group header, linked to the parent report on Pat_ID, RoundNumber and SurveyID.

Thanks but this didn't seem to get to what I want. I'll just try to lay it out how I want and if someone could walk me through how to build it that would be great. I would like the form to look something like this (the second column is where you would enter in you data)

And how I would like the tables to look is the following

Table1

Table2

Table3

Is there a way to enter in data once to a form and have it divvy up among their respective tables?

Perhaps this will help...

Survey Database by Duane Hookom

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3