Results 1 to 6 of 6

Thread: Columns to Rows

  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Post Unanswered: Columns to Rows

    Hi members,

    I am new to this Forum. I am beginner to MS Access.
    My data is in mentioned format.

    SOURCE TARGET
    1001 2924
    1001 2925
    1001 2921
    1001 2922
    2021 2022
    2021 9926
    2021 50221
    2021 50222
    2021 60521
    2021 60523
    2022 2021
    2022 2023
    2022 10021

    and I want to convert to.

    Source Target1 Target2 Target3 Target4 Target5 Target6
    1001 2924 2925 2921 2922
    2021 2022 9926 50221 50222 60521 60523
    2022 2021 2023 10021

    Thanks in advance for help.

    Regards
    jay

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Hello Jay

    Welcome to the list.

    In the attached example I have used code to produce the desired output. I assume there will be a maximum of 6 entries for each source number.

    See module mod_row_columns for the code

    The database will open with a form displaying the input and output. Make a change to the Input data, then click on the Run Code button and the Output will re-display with the updated data.
    Attached Files Attached Files

  3. #3
    Join Date
    Jul 2006
    Posts
    30

    Question Bad download

    Pappa Smurf
    Downloaded your file but could not open it. WinZip error message "does not appear to be a valid archive". Very interested .. . could you please re-post?

  4. #4
    Join Date
    Sep 2010
    Posts
    2
    Quote Originally Posted by Poppa Smurf View Post
    Hello Jay

    Welcome to the list.

    In the attached example I have used code to produce the desired output. I assume there will be a maximum of 6 entries for each source number.

    See module mod_row_columns for the code

    The database will open with a form displaying the input and output. Make a change to the Input data, then click on the Run Code button and the Output will re-display with the updated data.
    Hi Poppa Smurf,

    Thanks for Code. My input table is Big. Field1 has 60000 entries and max entries for any number is 60. I tried to do it with CrossTab Query. But I do not have the count with each entry. I mean if i can calculate Field1, like mentioned below.

    SOURCE Field1 TARGET
    1001 1 2924
    1001 2 2925
    1001 3 2921
    1001 4 2922
    2021 1 2022
    2021 2 9926
    2021 3 50221
    2021 4 50222
    2021 5 60521
    2021 6 60523
    2022 1 2021
    2022 2 2023
    2022 3 10021

    then i will use cross-tab query. Do you know any query in Access that can do this?

    Thanks & Regards
    Jay

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Jim

    Pappa Smurf
    Downloaded your file but could not open it. WinZip error message "does not appear to be a valid archive". Very interested .. . could you please re-post?
    I have the same problem sometimes using Winzip in Windows 7. I use 7zip to open zip when I have problems using Winzip.

    Anyway I have attached the file using 7zip. If you ahve problems contact me off list and I will send you the mdb file.

    Edit
    Note, this converts mutilple entries to one record as in the first posting in this thread. If you want to convert row and columns of an input table to columns and rows of the output table lookup Transpose on the Microsoft help, or I may still have an example for you.
    Attached Files Attached Files
    Last edited by Poppa Smurf; 09-12-10 at 21:48.

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Jay

    SOURCE Field1 TARGET
    Do you still want to transpose the rows to columns and count how many target fields are populated for each source

    e.g. after the transposing to columns the table could look like this

    Source Field1 Target1 .......Targetx
    1001 4 2924 2925 etc

    Where field1 is the number of entries for source 1001.

    OR
    Do you just want to count how many entries in your table for each source?

Posting Permissions

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