05-27-03, 14:53 #1Member
- Join Date
- May 2003
Unanswered: Convert from a Crosstab to Table format
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 number 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?
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.
05-28-03, 05:26 #2Registered User
- Join Date
- Oct 2002
- Leicester - UK
Re: Convert from a Crosstab to Table format
yes, here the code i used
Dim rs As DAO.Recordset, i As Integer
Set rs = CurrentDb.OpenRecordset("qryScrap")
While Not rs.EOF
For i = 2 To 49
If rs(i).Value > 0 Then DoCmd.RunSQL "INSERT INTO Scrap ( MachineID, PostingDate, ScrapCode, Quantity )SELECT '" & rs(0).Value & "' , " & CDbl(rs(1).Value) & " , " & Val(Right(rs(i).Name, 2)) & " , " & rs(i).Value & ";"
End SubDefinition of a Beginner, Someone who doesn't know the rules.
Definition of an Expert, Someone who knows when to ignore the rules.