Converting Access data into SQL Server 2000, don't think DTS can help

I would love some opinions on this one. I have an access database that I need to put into SQL. It's a database of patients and their responses to quality of life survey's. The way it is set up, is the patient ID, and the question ID make up the columns. There are seperate tables for each questionnaire we have that are set up this same way. So say there are 14 questions on a particular questionnaire, the table would have 15 columns, patient ID, and the 14 question ID's, with the response (0-4) going under the question ID. So we have the patient ID going in the table only once, and then a bunch of numbers going across. What I'd like to do, is have one big table, with 4 columns: Patient ID, questionnaire ID, question ID, and response. The patient ID will be repeated for as many of the questions as they answered. The hard part here is getting the columns in the old table to become rows in the new table. I'd like to dump all survey tables into this one new table. I need to write a script to take the access and put in the SQL DB. Put on your thinking caps everyone. I appreciate any and all responses. Thanks, Josh
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!