I am now totally confused. I am so close yet so far to finishing this project.
Microsoft say the folowing, which I have followed:
<start>
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 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:
Create Table2 according to the above structure, using the following data type criteria:
All Respondent ID fields must be the same data type.
The Question ID field must be a Text data type.
The Response field must be the same data type as the Question<x> data types.
Create a new query based on Table1.
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
Run Query1 to append to Table2 each participant's responses to Question1.
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.
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
<end>
The problem is that the append queries do not update automatically and once they have been updated once you cannot update them again as you get key errors.
I have uploaded my Db to
http://www.auto-alert.net/access/csi.zip in case this helps to allow you to see what I am doing wrong. I would be really grateful if you could point out my errors.