Results 1 to 4 of 4
  1. #1
    hoachen is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    17

    Horizontal to vertical data

    I am trying to make the data from multiple row to column. I tried using union but maybe I did not do it right and not get what I need. I also tried the crosstab. It gave me all the groupname on the header so i have scroll to the right for 100 columns where it did not align next to the InvName. It Here is the table data. The group# only have 3 groups. It has many different invName and the Group name about 100.

    Group# InvName Value GroupName
    1234 BS11 10.5 aabb
    1234 BS11 10.5 ccdd
    1234 BS4 5.5 hhee
    1234 BS11 10.5 ooppp
    1235 BC77 3.2 jjii




    And this is what I want it to be: GroupName1 GroupName2 GroupName3 does not matter it can be the same I just want it to seperate each groupname in the same row where the InvName is the same.

    Group# InvName Value GroupName1 GroupName2 GroupName3
    1234 BS11 10.5 aabb ccdd ooppp
    1234 BS4 5.5 ccdd
    1235 BC77 3.2 jjii nncc

    Just make all the same InvName and put the groupName in horizontal. Is this be done by using ms access? I have access 2007.

    Thank you very much in advance to read this post. You advice are appreciated.

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by hoachen View Post

    Group# InvName Value GroupName
    1234 BS11 10.5 aabb
    1234 BS11 10.5 ccdd
    1234 BS4 5.5 hhee
    1234 BS11 10.5 ooppp
    1235 BC77 3.2 jjii
    1) Not sure, but was wondering about the way data is stored. Wondering whether Group#,
    InvName andValue
    should be in a seperate table.
    2) Do you have an ID field in your table, primary key or auto-number.
    3) Try a Ranking query & then use it as source for a CrossTab query, with the Rank as Column Heading.

    Thanks

  3. #3
    hoachen is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    17
    Quote Originally Posted by recyan View Post
    1) Not sure, but was wondering about the way data is stored. Wondering whether Group#,
    InvName andValue
    should be in a seperate table.
    2) Do you have an ID field in your table, primary key or auto-number.
    3) Try a Ranking query & then use it as source for a CrossTab query, with the Rank as Column Heading.

    Thanks
    Thanks for your advice.

    I am not sure how to do the ranking in query.

    The actual data on two different tables. One contain Group#, InvName and the other are InvName and Value and GroupName. I am joining it these two tables by InvName.

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by hoachen View Post

    Group# InvName Value GroupName
    1234 BS11 10.5 aabb
    1234 BS11 10.5 ccdd
    1234 BS4 5.5 hhee
    1234 BS11 10.5 ooppp
    1235 BC77 3.2 jjii

    Group# InvName Value GroupName1 GroupName2 GroupName3
    1234 BS11 10.5 aabb ccdd ooppp
    1234 BS4 5.5 ccdd
    1235 BC77 3.2 jjii nncc
    Based on above Table & the results that you want, Just check out if below gives some guidelines :

    Save below query
    qryRanking

    Code:
    SELECT 
        A.TheGroup, 
        A.InvName, 
        A.TheValue, 
        A.GroupName, 
        Count(*) AS Rank
    FROM 
        Table1 AS A 
        INNER JOIN 
        Table1 AS B 
        ON 
        (A.GroupName>=B.GroupName) 
        AND 
        (A.InvName=B.InvName) 
        AND 
        (A.TheGroup=B.TheGroup)
    GROUP BY 
        A.TheGroup, 
        A.InvName, 
        A.TheValue, 
        A.GroupName;
    And
    Run the below final query :

    Code:
    TRANSFORM First(qryRanking.GroupName) AS FirstOfGroupName
    SELECT 
        qryRanking.TheGroup, 
        qryRanking.InvName, 
        qryRanking.TheValue
    FROM 
        qryRanking
    GROUP BY 
        qryRanking.TheGroup, 
        qryRanking.InvName, 
        qryRanking.TheValue
    PIVOT qryRanking.Rank;
    Edit :
    Pls note : Ranking queries are tricky (at least, I find them so). Be careful.

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. "transposing vertical data into horizontal columns"
    By stevelondon in forum Queries
    Replies: 3
    Last Post: 03-20-2012, 12:36 PM
  2. Replies: 1
    Last Post: 01-28-2011, 02:45 PM
  3. vertical text in datasheet view?
    By johncinpcb in forum Forms
    Replies: 0
    Last Post: 06-14-2010, 07:14 AM
  4. Vertical alignment of page Header
    By diane802 in forum Reports
    Replies: 6
    Last Post: 01-19-2010, 12:54 PM
  5. Vertical Fields on Report
    By jocallahan in forum Reports
    Replies: 3
    Last Post: 10-09-2008, 07:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums