This article was previously published under Q292299
Novice: Requires knowledge of the user interface on single-user computers.
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).
For a Microsoft Access 97 version of this article, see 101675.
For a Microsoft Access 2000 version of this article, see 209493.
This article describes a table design that you can use for an applicationthat tallies results from questionnaires and surveys.
The following examples outline nonrelational table design commonly used forquestionnaires and surveys, and suggests an improved, relational tabledesign.
Nonrelational Table Design
When designing tables for questionnaire applications, many users begin witha design that resembles the following table. Each record contains multiplefields, called Question1 through Question<n>, that contain responses to the questions.
Create a new stored procedure. To do so, click Queries under Objects in the Database window, click New, and then click Create Text Stored Procedure.
Type and then run the following statement:
Create Procedure StoredProc1 AS INSERT INTO Table2 (RespondentID, Response, QuestionID) SELECT Table1.RespondentID, Table1.Question1, 'Question1' AS Expr1 FROM Table1
Edit and run StoredProc1 for each question by opening the procedure in SQL view. For example, the stored procedure for the second question would read:
Alter Procedure StoredProc1 AS INSERT INTO Table2 (RespondentID, Response, QuestionID) SELECT Table1.RespondentID, Table1.Question2, 'Question2' AS Expr1 FROM Table1
Note that the keyword "Create" is used in the initial instance; the keyword "Alter" is used in subsequent instances.
To display the number of positive responses, create a stored procedure similar to step 2, and then run the following statement:
Create Procedure Ayes As SELECT QuestionID Question, COUNT(ALL QuestionID) [Yes count] FROM Table2 WHERE Response = 1 GROUP BY QuestionID
To display the number of negative responses, create a stored procedure similar to step 2, and then run the following statement:
Create Procedure Noes As SELECT QuestionID Question, COUNT(ALL QuestionID) [No count] FROM Table2 WHERE Response = 0 GROUP BY QuestionID
For more information about append queries, click Microsoft Access Help on the Help menu, type create an append query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
cross tab append questionnaire flat file survey gathering information how to gather summary add up adds