Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Crosstab Table (Access 2000)

    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.
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Convert Crosstab Table (Access 2000)

    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?

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Crosstab Table (Access 2000)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Crosstab Table (Access 2000)

    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.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Crosstab Table (Access 2000)

    Perfect .... many many thanks!

Posting Permissions

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