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