Results 1 to 7 of 7
  1. #1
    jbuckner72 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    12

    MS Access combine multi row into single row, multi columns

    I have a query that ends up looking like this:
    Click image for larger version. 

Name:	qCURRENT.PNG 
Views:	19 
Size:	10.9 KB 
ID:	40383

    And I need to make it look like this:


    Click image for larger version. 

Name:	qFUTURE.PNG 
Views:	20 
Size:	6.0 KB 
ID:	40384

    Using Access 365 64-bit

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    How you achieve that will depend on how the original data is stored in your table(s).
    However it is likely you will need a crosstab query.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    jbuckner72 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    12
    Thanks. Playing around with the crosstab query. By the time I get to the point of what I showed, there are queries upon queries upon queries. So my data structure this query runs from is flat. Basically a row for every product used by customer. Essentially the first image I put is the data structure I am running against.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Like this?

    Click image for larger version. 

Name:	Query1.png 
Views:	18 
Size:	76.9 KB 
ID:	40391
    ================================================== ================================================== ==========

    Changing Query1 to a Totals query:

    Click image for larger version. 

Name:	Query2.png 
Views:	18 
Size:	73.9 KB 
ID:	40392



    BTW, you do know that your table structure is not normalized, don't you??? "Product A", "Product B" and "Product C" is data. What will you have to do to add "Product D"??

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    very odd that this problem is so similar to this one
    https://www.accessforums.net/showthread.php?t=79055

    data is different, but problem the same. Almost looks like school stuff. Anyway, I suggested self joined tables.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Agree with Micron's analysis. I believe a Power Query Solution is the way to go.

  7. #7
    jbuckner72 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    12
    This worked perfectly ssanfu. Before I read this, I had created a lot of crosstab queries, then pulled them all together later, but this did it in one step. Thanks very much.

    Yeah my source data is garbage unfortunately, but it is from an external system so i have to cram it into this.

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

Similar Threads

  1. multi column lookup single value
    By davedinger in forum Forms
    Replies: 5
    Last Post: 08-26-2018, 11:38 PM
  2. Multi-dimensional/Multi-type Array
    By Ellpee in forum Programming
    Replies: 5
    Last Post: 01-28-2016, 05:58 PM
  3. Replies: 6
    Last Post: 09-10-2012, 07:19 AM
  4. Replies: 2
    Last Post: 08-31-2012, 02:20 PM
  5. Switch combobox from multi back to single
    By soulice in forum Access
    Replies: 1
    Last Post: 04-08-2012, 01:18 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