Results 1 to 2 of 2

Convert data in rows to column headings?

This is a discussion on Convert data in rows to column headings? within the Microsoft Access forums, part of the Question Forums category; I've been out of the Access loop for a while, and have a quick question. The data I have is ...

  1. #1
    Board Regular
    Join Date
    Sep 2006
    Posts
    528

    Default Convert data in rows to column headings?

    I've been out of the Access loop for a while, and have a quick question.

    The data I have is in the following format:

    ID Date Total
    1234 01/01/1985 524.98
    1234 01/01/1986 564.12
    1234 01/01/1987 112.25
    2346 01/01/1985 475.56
    8476 01/01/1985 789.52
    8476 01/01/1986 445.55
    8476 01/01/1987 123.45
    2946 01/01/1985 123.85
    1234 01/01/1988 987.65
    9927 01/01/1985 127.54
    9927 01/01/1986 445.85

    I want to run a query, or set up a new table, that translates the data into this format:

    ID 01/01/1985 01/01/1986 01/01/1987 01/01/1988 ....
    1234 524.98 564.12 112.25 987.65 ....
    2346 ...
    8476 ...
    2946 ...
    9927 ...

    Any suggestions?
    Regards,
    Tim Morrill

  2. #2
    Board Regular dodom75's Avatar
    Join Date
    Jul 2008
    Location
    North Carolina
    Posts
    129

    Default Re: Convert data in rows to column headings?

    I think you are wanting a Cross Tab query. You have three options here.

    1. Easy Option:
    You can export the information to excel and then copy the information, open a blank spreadsheet, choose "Paste Special" and click the transpose button and it converts the data.

    2. Other Option:
    Build A report in Access with all your column headings going down the left side:
    Date:
    ID:
    Total:


    3. Click, New Query, Choose "Crosstab Query". This only allows you to have three columns.


    Keep in mind for options 1 & 2 you have a maximum of 256 columns so if you have more rows of data than 256 then you are going to get an error.

    I hope this helps.


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com