If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table Error Message | Di | New Users | 2 | June 30th, 2004 07:57 AM |