Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/101675

From BetaArchive Wiki

Article ID: 101675

Article Last Modified on 1/18/2007


  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition

This article was previously published under Q101675

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


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


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 table below. Each record contains multiple fields, called Question1 through Question<n>, that contain responses to the questions.

   Table: Table1 (old)
   FieldName: Respondent ID [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 table below, the Question ID field identifies the question and the Response field contains the answer.

   Table: Table2 (new)
   Field: Respondent ID [Primary Key]
   Field: Question ID   [Primary Key]
   Field: Response

How to Switch to Relational Database Design

To convert data that has been entered in Table1 format, follow these steps:

  1. Create Table2 according to the above structure, using the following data type criteria:

    1. All Respondent ID fields must be the same data type.
    2. The Question ID field must be a Text data type.
    3. The Response field must be the same data type as the Question<x> data types.
  2. Create a new query based on Table1.
  3. From the Query menu, choose Append Query (Append in versions 7.0 and earlier) Select Table2 as the table to which you want to append the data. Design the query as follows:

          Query: Query1
          Field: Respondent ID
             Append To: Respondent ID
          Field: Question1
             Append To: Response
          Field: "Question1"
             Append To: Question ID
  4. Run Query1 to append to Table2 each participant's responses to Question1.
  5. Repeat steps 2-4 above, replacing Question1 with Question2, and "Question1" with "Question2". You must re-create or modify this query for each question in Table1.
  6. After running all the append queries, the result is a table (Table2) that can easily summarize your results in a crosstab query:

          Query: CrossTabExample
          Field: Question ID
             Total: Group By
             Crosstab: Row Heading
          Field: Response
             Total: Group By
             Crosstab: Column Heading
          Field: Response
             Total: Count
             Crosstab: Value


For more information about append queries, search the Help Index for "append queries," or ask the Microsoft Access 97 Office Assistant.

Additional query words: cross tab append questionnaire flat file survey

Keywords: kbinfo kbusage KB101675