Normalizing table with repeating groups and existing relationships - How?

Folks,

I have inherited an Access 2000 database that contains about 50
records. The database is not normalized, but I'd like to make it so
without having to reenter the existing data.

My main concern is normalizing a table that contains questionnaire
reponses. Currently the table's fields are set up like so:

QuestionnaireID (autonumber primary key)
Item1
Item2
Item3

I am a novice at Access, although I do understand normalization.
Assuming it's possible to normalize this table without losing data and
fouling up relationships with other tables, could someone explain how?
The simpler your explanation, the better.

TIA,

Steve

Here is an outline of how I would go about this. I hope it
helps

Step 1 - Design a New Normalised Table - tblNewResponse
questionnaireId - Primary Key
itemNumber - Primary Key
response

Step 2 - Populate New Table with Item 1 Responses
INSERT INTO tblNewResponse (questionnaireId, itemNumber,
response)
SELECT questionnaireId, 1, item1
FROM tblResponse
WHERE item1 IS NOT NULL

Step 3 - Populate New Table with Item 2 Responses
INSERT INTO tblNewResponse (questionnaireId, itemNumber,
response)
SELECT questionnaireId, 2, item2
FROM tblResponse
WHERE item2 IS NOT NULL

Step 4 - Populate New Table with Item 3 Responses
INSERT INTO tblNewResponse (questionnaireId, itemNumber,
response)
SELECT questionnaireId, 3, item3
FROM tblResponse
WHERE item3 IS NOT NULL

Carry on in this manner until all items have been copied
into the new table.

Step 5 - Carry out some integrity checks to ensure that the
table has been copied across correctly.

Step 6 - Rename tblResponse to something like
tblResponse_Old and rename tblNewResponse to tblResponse

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Folks,

I have inherited an Access 2000 database that contains
about 50
records. The database is not normalized, but I'd like to
make it so
without having to reenter the existing data.

My main concern is normalizing a table that contains
questionnaire
reponses. Currently the table's fields are set up like so:

QuestionnaireID (autonumber primary key)
Item1
Item2
Item3

I am a novice at Access, although I do understand
normalization.
Assuming it's possible to normalize this table without
losing data and
fouling up relationships with other tables, could someone
explain how?
The simpler your explanation, the better.

TIA,

Steve
.

On Sat, 14 Aug 2004 11:48:42 -0700, "Gerald Stanley"
- wrote:

Here is an outline of how I would go about this. I hope it
helps

Gerald,

Thank you very much. I'll ponder this and give it a whirl.

Steve