Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: transpose colum into rows

    I am looking to create a query to transpose my union query into rows based on two columns. I'm starting with 7 columns that I need to group on all the matching data in columns 1 through 5, then count how many rows this group has on column 6 and 7. The max allowed in each group is 10...so the new query will have create column1A and column2A up to column1J and column column2J, allowing blanks whereever.
    I am not summing anything

    I have pasted a sample of my union query results and what I am trying to accomplish in the new query.
    this is a sample of my union query results:
    sl1 sl2 sl3 acct name invno amt
    150 12345 ABC Company IN099999997 $100.00
    150 12345 ABC Company IN099999998 $150.00
    150 12345 ABC Company IN099999999 $200.00
    117 134 12449 123 Company IN088888867 $10.00
    117 134 12449 123 Company IN088888967 $1,000.00
    220 12549 Heidi's Business IN099999972 $63.99


    I need a query to look like this:
    sl1 sl2 sl3 acct name invno1 amt1 invno2 amt2 invno3 amt3
    150 12345 ABC Company IN099999997 $100.00 IN099999998 $150.00 IN099999999 $200.00
    117 134 12449 123 Company IN088888867 $10.00 IN088888967 $1,000.00
    220 12549 Heidi's Business IN099999972 $63.99

    I can't seem to get my head around how I need to do this.
    I would like to keep it within access - and the new query is exported to excel to be used in a word mail merge

    can someone point me in the right direction?
    Attached Files Attached Files

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Before you even think about an answer to your question, think about the answer to this one: what are you going to do when you have 30 invoice numbers and amounts? Are you going to extend them 60 columns?

    You might want to re-think your scheme.

    Sam

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    This is a database I use - The data in the union query is so isolated - it will NEVER be that big.
    For 4 years - it has been being retyped into a table with columns invoice1, amt1, etc.........I've never needed more than 5 results per acct

    I created the queries to return exactly what I need, so was hoping I don't have to re-type it.

    now I just need the tools to write the sql properly

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    What I do when I need this is to read the data into an array. Then I make a new blank table with the max sideways number of columns. With VBA I read the data from the array into the new sideways table. Then copy or export as necessary.

    Sam

  5. #5
    Join Date
    Jun 2010
    Posts
    186
    Hi thanks - so it can be done!
    hmm - I've no experience with that - not really sure how to start.
    The data is very minimal and simple - I was hoping to use sql
    I'm studying arrays in help - can't hurt to learn new technique!

    Thanks again

Posting Permissions

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