How to design a sample table to support questionnaire applications in Access 2002

This article was previously published under Q292299
This article has been archived. It is offered "as is" and will no longer be updated.
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.
SUMMARY
This article describes a table design that you can use for an applicationthat tallies results from questionnaires and surveys.
MORE INFORMATION
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.
   Table: Table1 (old)   --------------------------------------   FieldName: RespondentID [Primary Key]   FieldName: Question1   FieldName: Question2   FieldName: Question3   .   .   .   FieldName: Question<n>				
Problems occur when you want to perform crosstab queries to summarizeor graph the questionnaire results (or both).

Relational Table Design

A relational table design better supports summary queries, reports, andgraphs. 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				

How to Switch to Relational Database Design

Steps Using an Access Database.

  1. Create the following table:
       Table: Table2   ----------------------------   Column Name: RespondentID   Datatype: Integer   Column Name: QuestionID   Datatype: text   Column Name: Response   Datatype: yes/no   Table Properties: Table2   ------------------------------------   PrimaryKey: RespondentID, QuestionID					
  2. Create a new query that is based on Table1.
  3. 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					
  4. 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.
  5. After running all the append queries, the result is a table (Table2) that can easily summarize your results in a totals query:
       Query: qryResults   ----------------------------------------------------   Field: QuestionID   Table: Table2   Total: Group By   Field: Ayes: Abs(Sum([Response]=Yes<AngularNoBind>}}</AngularNoBind>   Total: Expression   Field: Noes: Count([Response]) + Sum([Response]=Yes)   Total: Expression					

Steps Using an Access Project.

  1. Create the following table:
       Table: Table2   -------------------------   Column Name: RespondentID   Datatype: int   Column Name: QuestionID   Datatype: nvarchar   Column Name: Response   Datatype: bit   Table Properties: Table2   ------------------------------------   PrimaryKey: RespondentID, QuestionID					
  2. 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.
  3. 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					
  4. 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.
  5. 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					
  6. 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					
REFERENCES
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
Properties

Article ID: 292299 - Last Review: 12/06/2015 01:23:56 - Revision: 3.1

Microsoft Access 2002 Standard Edition

  • kbnosurvey kbarchive kbhowto KB292299
Feedback