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

Back to the top

Nonrelational Table Design

   Table: Table1 (old)
   --------------------------------------
   FieldName: RespondentID [Primary Key]
   FieldName: Question1
   FieldName: Question2
   FieldName: Question3
   .
   .
   .
   FieldName: Question<n>

				

Back to the top

Relational Table Design

   Table: Table2 (new)
   ----------------------------------
   Field: RespondentID [Primary Key]
   Field: QuestionID   [Primary Key]
   Field: Response
				

Back to the top

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

Back to the top