Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Johannesburg, South Africa
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transpose fields in MSAccess records

    Can anyone help with the the problem statement I have shown on the attached excel spread sheet. I have tried (but failed) in trying to do this with a cross tab query. Is there any way I can achieve this natively inside of Access where the query shown in the attached Excel file as a table was created.

    Thank you, and looking forward to some clues.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I believe if you combine some of the columns A through F and using a delimiter character, then those could be the rows specified in a crosstab. (Note that the wizard only supports three Row Headings - however if you are comfortable editing the actual SQL of the query you can add more.) Then you can create a fixed column header definition containing the integers from 1 through 20, and you combine columns H and I and use a delimiter character to separate them, that would be used as the Value parameter. If the data needs to be separated once the crosstab has been created, you could export it to Excel and use the text to column feature to separate them. If that isn't acceptable, see http://www.tek-tips.com/faqs.cfm?fid=4524 for a description of how to get a Crosstab to return two columns for each column header value.
    Wendell

Posting Permissions

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