Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    26

    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?

    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.

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Re: Convert from a Crosstab to Table format

    yes, here the code i used
    Sub makescraptable()
    Dim rs As DAO.Recordset, i As Integer
    Set rs = CurrentDb.OpenRecordset("qryScrap")
    rs.MoveFirst
    DoCmd.SetWarnings False
    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 & ";"
    Next
    rs.MoveNext
    Wend
    DoCmd.SetWarnings True
    End Sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •