Microsoft KB Archive/209493

= ACC2000: Sample Table Design to Support Questionnaire Applications =

Article ID: 209493

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209493



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 2002 version of this article, see 292299.



SUMMARY
This article describes a table design that you can use for an application that tallies results from questionnaires and surveys.



MORE INFORMATION
The following examples outline nonrelational table design commonly used for questionnaires and surveys, and suggests an improved, relational table design.

Nonrelational Table Design
When designing tables for questionnaire applications, many users begin with a design that resembles the following table. Each record contains multiple fields, called Question1 through Question, that contain responses to the questions.   Table: Table1 (old) --  FieldName: RespondentID [Primary Key] FieldName: Question1 FieldName: Question2 FieldName: Question3 .  .   .   FieldName: Question Problems occur when you want to perform crosstab queries to summarize and/or graph the questionnaire results.

Relational Table Design
A relational table design better supports summary queries, reports, and graphs. In the following table, the Question ID field identifies the question and the Response field contains the answer.   Table: Table2 (new) --  Field: RespondentID [Primary Key] Field: QuestionID  [Primary Key] Field: Response

Steps Using an Access Database.
  Create the following table structure:   Table: Table2 Column Name: RespondentID Datatype: Integer

Column Name: QuestionID Datatype: text

Column Name: Response Datatype: yes/no

Table Properties: Table2 ---  PrimaryKey: RespondentID, QuestionID  Create a new query based on Table1.  On the Query menu, click Append Query. Select Table2 as the table to which you want to append the data. Design the query as follows:      Query: Query1 ---     Field: RespondentID Append To: RespondentID Field: Question1 Append To: Response Field: "Question1" Append To: QuestionID </li> Run Query1 to append to Table2 each participant's responses to Question1.

Repeat steps 2 through 4, replacing Question1 with Question2, and "Question1" with "Question2". Modify and run this query for each question in Table1.</li>  After running all the append queries, the result is a table (Table2) that can easily summarize your results in a totals query: <pre class="fixed_text">  Query: qryResults ---

Field: QuestionID Table: Table2 Total: Group By

Field: Ayes: Abs(Sum([Response]=Yes}} Total: Expression

Field: Noes: Count([Response]) + Sum([Response]=Yes) Total: Expression

</li></ol>

Steps Using an Access Project.
<ol>  Create the following table structure: <pre class="fixed_text">  Table: Table2 -  Column Name: RespondentID Datatype: int

Column Name: QuestionID Datatype: nvarchar

Column Name: Response Datatype: bit

Table Properties: Table2 PrimaryKey: RespondentID, QuestionID </li>  Create and run the following stored procedure: <pre class="fixed_text">  Create Procedure StoredProc1 AS  INSERT INTO Table2 (RespondentID, Response, QuestionID) SELECT Table1.RespondentID, Table1.Question1, 'Question1' AS Expr1 FROM Table1 </li>  Edit and then run StoredProc1 for each Question. For example, the stored procedure for the second question would read: <pre class="fixed_text">  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. </li>  To display the number of positive responses, create and run the following stored procedure: <pre class="fixed_text">  Create Procedure Ayes As  SELECT QuestionID Question, COUNT(ALL QuestionID) [Yes count] FROM Table2 WHERE Response = 1 GROUP BY QuestionID </li>  To display the number of negative responses, create and run the following stored procedure: <pre class="fixed_text">  Create Procedure Noes As  SELECT QuestionID Question, COUNT(ALL QuestionID) [No count] FROM Table2 WHERE Response = 0 GROUP BY QuestionID </li></ol>

<div class="references_section">