Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Transform data from records to columns - difficult query

    Hi Guys,

    Please find sample database attachment.

    There is a table:

    Click image for larger version. 

Name:	Screenshot_53.png 
Views:	40 
Size:	53.6 KB 
ID:	35116

    I want to get result like this:

    Click image for larger version. 

Name:	Screenshot_54.png 
Views:	40 
Size:	11.3 KB 
ID:	35117


    It is possible at all?

    Best,
    Jacek
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Did you try a Crosstab query?

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    I tried but i do not know how to do it...

    Best,
    Jacek

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you Orange,

    i tried to do it like here:

    Click image for larger version. 

Name:	Screenshot_56.png 
Views:	38 
Size:	52.8 KB 
ID:	35118

    But there is only 2 records for Work1 and Work2...

    Best,
    Jacek

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can always use the Query Wizard.
    it will guide you.

  7. #7
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    I tried using wizard and manually. I do not know how to do it...

    So i am writing here...

    Best,
    Jacek

  8. #8
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    I tried with:

    Click image for larger version. 

Name:	Screenshot_13.png 
Views:	29 
Size:	68.2 KB 
ID:	35129

    Click image for larger version. 

Name:	Screenshot_15.png 
Views:	29 
Size:	49.6 KB 
ID:	35130

    but still this is not what i want.
    Maybe i have to add some additional column?

    Best,
    Jacek

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Consider:

    TRANSFORM First(Workload.MemoryGiB) AS FirstOfMemoryGiB
    SELECT DCount("*","Workload","[Workload] & [isTrue]='" & [Workload] & [isTrue] & "' AND TableSummaryID<" & [TableSummaryID])+1 AS GrpSeq
    FROM Workload
    GROUP BY DCount("*","Workload","[Workload] & [isTrue]='" & [Workload] & [isTrue] & "' AND TableSummaryID<" & [TableSummaryID])+1
    PIVOT [WorkLoad] & [isTrue];

    or

    TRANSFORM First(Workload.MemoryGiB) AS FirstOfMemoryGiB
    SELECT DCount("*","Workload","[Workload] & [isTrue]='" & [Workload] & [isTrue] & "' AND Val(MemoryGIB)<" & Val([MemoryGIB]))+1 AS GrpSeq
    FROM Workload
    GROUP BY DCount("*","Workload","[Workload] & [isTrue]='" & [Workload] & [isTrue] & "' AND Val(MemoryGIB)<" & Val([MemoryGIB]))+1
    PIVOT [WorkLoad] & [isTrue];

    Why isn't MemoryGIB a number field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi June7,

    thank you, awesome,

    i have still one problem here - GiB are not sorted.

    Can you also explain a little code in very simple english? I do not understand it.

    TRANSFORM First(Workload.MemoryGiB) AS FirstOfMemoryGiB --> Agregating function where you are using function first (which gives first value). Why to use this?

    SELECT DCount("*","Workload","[Workload] & [isTrue]='" & [Workload] & [isTrue] & "' AND TableSummaryID<" & [TableSummaryID])+1 AS GrpSeq --> this is awesome. This is grouping workloads and isTrue value. But how i working : TableSummaryID<" & [TableSummaryID]? I can nto imagine this

    PIVOT [WorkLoad] & [isTrue]; --> This means that these fields go into columns?

    Best,
    Jacek





  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you try the second version?

    Some aggregate function must be used on the data. First works because there is only one GIB for each Workload & isTrue combination. Max, Min, Last should give same result.

    The DCount counts records where the ID is less than ID of current record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    A sorry,

    the second method is working like a charm!

    thank you.

    The DCount counts records where the ID is less than ID of current record.
    sorry i have to understand this.
    So I have :

    Click image for larger version. 

Name:	Screenshot_57.png 
Views:	20 
Size:	26.5 KB 
ID:	35145

    And how is ID less then current record?

    TableSummaryID = 2 so less then current record is 1?

    Best,
    Jacek

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Right, and if there isn't a lower number the count is 0 and that's why the expression has +1.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    thank you,

    so when you have next set of Workloads and tiers with SummaryTableID = 5, there should be 4.

    And 4 + 1 = 5....

    Hmm i am not seeing it.

    Best,
    Jacek

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    There could be 4 records before ID 5 unless there are gaps in sequence. Not quite sure what you are asking.

    Try <= and eliminate +1.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Transform Data .. pls help!
    By Toble in forum Queries
    Replies: 14
    Last Post: 02-23-2015, 05:34 PM
  2. Replies: 17
    Last Post: 06-10-2013, 06:49 AM
  3. Difficult Query!
    By pastormcnabb in forum Queries
    Replies: 2
    Last Post: 04-09-2013, 09:40 PM
  4. difficult query
    By methis in forum Queries
    Replies: 2
    Last Post: 03-18-2013, 02:36 PM
  5. Transform data model from Access to X
    By snoopy in forum Database Design
    Replies: 2
    Last Post: 05-29-2012, 12:37 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