Did you try a Crosstab query?
I tried but i do not know how to do it...
Best,
Jacek
you can always use the Query Wizard.
it will guide you.
I tried using wizard and manually. I do not know how to do it...
So i am writing here...
Best,
Jacek
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.
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
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.
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.
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
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.