ACC2000: Sample Table Design to Support Questionnaire Applications

ID: Q209493

The information in this article applies to:

Microsoft Access 2000

Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

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<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 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

How to Switch to Relational Database Design

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

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.

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}}
   Total: Expression

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

Steps Using an Access Project.

Create the following table structure:


   Table: Table2
   -------------------------
   Column Name: RespondentID
   Datatype: int

   Column Name: QuestionID
   Datatype: nvarchar

   Column Name: Response
   Datatype: bit

   Table Properties: Table2
   ------------------------------------
   PrimaryKey: RespondentID, QuestionID

Create and run the following stored procedure:


   Create Procedure StoredProc1
   AS
   INSERT INTO Table2 (RespondentID, Response, QuestionID) 
   SELECT Table1.RespondentID, Table1.Question1, 'Question1' AS Expr1
   FROM Table1

Edit and then run StoredProc1 for each Question. 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 and run the following stored procedure:


   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 and run the following stored procedure:


   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 append in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: cross tab append questionnaire flat file survey

Keywords : kbusage kbdta TblDsign
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto

Send feedback to MSDN.

Look here for MSDN Online resources.