Normalizing table with repeating groups and existing relationships - How?

  • Thread starter Thread starter Steve Newton
  • Start date Start date
S

Steve Newton

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