Results 1 to 7 of 7
  1. #1
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48

    Put columns to rowa

    Hi everyone,



    I've created a query where I am aggregating numbers by months, like this


    Code:
    SELECT field1, field2, field3, Year, Month, SUM(Amount) as Total
    WHERE (field1 ="aa" And field2="bb" AND field3="cc")
    AND ((Year = 2017) AND (Month =September) OR ((Year = 2018) AND mONTH = July)) 
    GROUP BY field1, field2, field3, Year, Month
    Actually it works, but I would need the records in different structure, I would need the Year and Month fields in separate columns, instead of repeating the records. I mean something like this:

    Current:
    Field1; Field2;Field3;2017; September;500
    Field1; Field2;Field3;2018;Juni;750

    To be:
    Field1; Field2;Field3;2017; September;500;Juni;750

    Is there a way to manage it in MSAccess? I run this from excel vba, so I could manipulate it with iterating throught the recordset, but I wonder if there is a more reliable way to do it.
    Thank you

  2. #2
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    I think (and I'm not sure) that you would need two separate queries, one to get the first line of data and one to get the second. You'd then need a third query to join the two lots of data.

  3. #3
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    Sounds tricky...
    Thanks for your help.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Always better to post your actual code - the code you have provided clearly won't work - there is no FROM clause

    but in principle you might have something like

    Code:
    SELECT field1, field2, field3, iif(year=2017,year) as Yr, iif(month=September,month) as M1, SUM(iif month=September,Amount)) as M1Ttl, iif(month=July,month)) as M2, SUM(iif(month=July,Amount))as M2Ttl
    FROM myTable
    WHERE (field1 ="aa" And field2="bb" AND field3="cc")
    AND ((Year = 2017) AND (Month =September) OR ((Year = 2018) AND mONTH = July)) 
    GROUP BY field1, field2, field3,iif(year=2017,year), iif(month=September,month), iif(month=July,month)

    Note Year and Month are reserved words and should not be used for field names tho I'm guess this is all pseudo code

  5. #5
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    Thank you, I am going to try it, however this way of querying is new for me.
    Could you recommend some keyword to search for so that I understand the background?
    Thanks again.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure of any specifc keyword - its an aggregate (group by) query with conditional aggregation (summing, counting, etc)

  7. #7
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    Thanks a lot for your help, I really appreciate it.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-24-2017, 05:16 PM
  2. Replies: 2
    Last Post: 04-13-2017, 02:49 PM
  3. Replies: 2
    Last Post: 10-26-2015, 06:14 AM
  4. Dynamic Columns - Import new columns?
    By memaxt in forum Import/Export Data
    Replies: 2
    Last Post: 06-24-2014, 06:02 AM
  5. Replies: 2
    Last Post: 05-15-2014, 06:15 AM

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