Convert Crosstab Table (Access 2000)

Topic

New Reply

I am working with survey results which are downloaded from a web provider. These results are in a CSV (comma separated values) file and presented in a crosstab / matrix type format (see attached Zip file containing the document Survey.CSV).

The numbers of questions are fixed. I can import the CSV file to a table without any problem. However is there anyway, via code, to append this data to another table that has the following fields?

Question
Response

Using the attachment as an example, I would like to have the values of cells D1 through AF1 appended to the Question field and the values of D2 through DF2 appended to the Response field.

Does anyone know if this is possible?

Any assistance and/or guidance would be greatly appreciated.

Reply | Quote

Replies

I have double clicked on the csv file and it goes into excel.
What is the question field, is it a table or a field in a table?
Also what is the response field, is it a table or a field in a table?
Fields D1 – AF1 are in fact headings in the excel sheet, while D2 – AF2 are data values.
There are rows 3 and 4 as well, what are these used for?

Reply | Quote

CSV files can be opened with Excel or imported into other applications. I import the CSV file into an Access table named

Reply | Quote

I have attached your database with some modifications. I have split off a separate table SurveyNumbers containing Survey Number, Email and Date. There is no need repeating Email and Date in each record of the Survey table.
The Survey table now only holds Survey Number, Question and Response. I also created relationships between the tables.

The module basTransform contains a procedure Transform that fills tblQuestion (but not the Question field, because that info is not present in CSVImport), SurveyNumbers and Survey. The procedure opens ADO recordsets for each of the tables; it loops through the records of CSVImport and through its fields.

Reply | Quote

Perfect …. many many thanks!

Reply | Quote