Help manipulating a Pivot Table 1

[VickyC]

greetings

I need to process data in Pivot Tables that look like this...

 [b]
        (pk)
        TypeID    2667    3771    4998    8772    9911  [/b]
[b]tblA[/b]      1                22      13   
          2         5               8      27
          3                13                   
          4        10                       7      12
          5 etc...

I need a query that will organize the data like this... (empty rows are just for clarity)

 [b]
        (     pk      )
        TypeID   Detail    TblValue   PivotValue[/b] 
[b]tblB[/b]      1        1           22       3771
          1        2           13       4998

          2        1            5       2667
          2        2            8       4998
          2        3           27       8772

          3        1           13       3771

          4        1           10       2667
          4        2            7       8772
          4        3           12       9911

          5 etc...

Every Pivot Table I'm given has different fields after the TypeID field, of course, and this is making it hard to produce the desired tblB.

Thanks for any clues.

 

[PHV]

You don't have access to the source data of the pivot tables ?
If not, I'm afraid you have to use VBA with RecordSet.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

mmmmmmm... that's what I was worried about. Thanks PHV

 

[VickyC]

hi again PHV and others. As PHV mentioned, I don't have direct access to the query that was used to create the Pivot Table. But, I can access 'earlier' tables that were used to create the Pivot Table. For the case I posted earlier, it looks like...

 [b]

Set     CountOfValue      Value [/b]
100         22             3771
100         13             4998

101         22             3771
101         13             4998

102          5             2667
102          8             4998 
102         27             8772 

103         22             3771
103         13             4998

104         13             3771

105          5             2667
105          8             4998 
105         27             8772 

106         10             2667
106          7             8772
106         12             9911

107         22             3771
107         13             4998

108         13             3771

109 etc...

These tables make no mention of the field TypeID shown in my original Pivot Table. Here's why...
You'll notice in the table above that there are Sets where the CountOfValue and the Value entries are exact matches. For example, Set 100, 101, 103, and 107 are exact matches. So, Set 100 is assigned TypeID = 1, and the other matching Sets are dropped. Similarly, Set 102 is assigned TypeID = 2, and the matching Set 105 is dropped. TypeID = 3 and TypeID = 4 belong to Sets 104 and 106 respectively.

I believe I could solve the problem stated in my original posting based on tables like the one above, but I'm stymied by the fact that Sets can have more or less records. If they all had the same number of records, I think the solution would be a lot easier.

I'm grateful for any help! Vicky C.

 

[PHV]

What about this (you have to put the real name of EarlierTable) ?

SELECT X.TypeID,Count(*) AS Detail,X.CountOfValue AS tblValue,X.Value AS PivotValue
FROM (SELECT Count(B.MinSet) AS TypeID,A.CountOfValue,A.Value
FROM (SELECT Min(Set) AS MinSet,CountOfValue,Value FROM EarlierTable GROUP BY CountOfValue,Value ORDER BY 1,3
) A INNER JOIN (SELECT DISTINCT MinSet FROM (SELECT Min(Set) AS MinSet FROM EarlierTable GROUP BY CountOfValue,Value) C
) B ON A.MinSet>=B.MinSet
GROUP BY A.MinSet,A.Value,A.CountOfValue) X
INNER JOIN (SELECT Count(B.MinSet) AS TypeID,A.CountOfValue,A.Value
FROM (SELECT Min(Set) AS MinSet,CountOfValue,Value FROM EarlierTable GROUP BY CountOfValue,Value ORDER BY 1,3
) A INNER JOIN (SELECT DISTINCT MinSet FROM (SELECT Min(Set) AS MinSet FROM EarlierTable GROUP BY CountOfValue,Value) C
) B ON A.MinSet>=B.MinSet
GROUP BY A.MinSet,A.Value,A.CountOfValue) Y ON X.TypeID=Y.TypeID AND X.Value>=Y.Value
GROUP BY X.TypeID,X.Value,X.CountOfValue

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

hi PHV - that is REALLY NICE code. It worked perfectly!

The fun part was dissecting it afterwards. After printing it out with levels of indentation, I broke it up into 15 small qdfs (5 are repeated twice), then examined how the output is modified at each level. (Really instructive to anyone wanting to develop their SQL skills!)

Here's 2 simple follow-up questions...
a) When using a query like this to process large tables, is their any advantage to 'stringing together' a series of simple queries based on other simple queried - as opposed to combining them into 1 qdf as you've done?

b) I noticed that the blocks of code above and below the 'middle' INNER JOIN represent the joining of an output table to a copy of itself, to facilitate ranking. In doing this, you used the same set a alias values (A, B, C) in both blocks. Does the query processor repeat the computation for both of these identical code blocks, or does the fact that you used the same alias values 'help' it to be more efficient by only computing the block's output once? Just wonderin'

Thanks again for some very nice code. Vicky C.

 

[PHV]

Initially I've tested a solution to your problem with three queries:

SELECT Min(Set) AS MinSet, CountOfValue, Value
FROM EarlierTable
GROUP BY CountOfValue, Value
ORDER BY 1, 3

SELECT Count(B.MinSet) AS TypeID, A.CountOfValue, A.Value
FROM qryNoDupEarlierTable AS A INNER JOIN (SELECT DISTINCT MinSet FROM qryNoDupEarlierTable
)  AS B ON A.MinSet >= B.MinSet
GROUP BY A.MinSet, A.Value, A.CountOfValue

SELECT A.TypeID, Count(*) AS Detail, A.CountOfValue AS tblValue, A.Value AS PivotValue
FROM qryTypeID AS A INNER JOIN qryTypeID AS B ON A.TypeID = B.TypeID AND A.Value >= B.Value
GROUP BY A.TypeID, A.Value, A.CountOfValue

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

PHV - I can see how that approach makes sense.

Here's something curious. The 1st line of your final SQL is...

SELECT X.TypeID, Count(*) AS Detail, ...etc

My final output labels the 2nd column as Field0, not Detail. If I change the name to, say, TypeDetail, there's no problem.

I'm not sure why the word 'Detail' isn't accepted - I don't see it on the list of reserved words for MS Access or for SQL???

Vicky C.

 

[VickyC]

PHV - I've run your code in a series of trials, and it was perfect in all cases but one. I can identify where the glitch is occurring, but I'm not sure it will be easy to fix.

Consider the original starting table (below left) and the slightly modified table (below right). The ONLY change is indicated in red...

[b]tbl_Original                                tbl_Modified

Set     CountOfValue      Value             Set     CountOfValue      Value [/b]
100         22             3771             100         22             3771
100         13             4998             100         13             4998

101         22             3771             101         22             [b][COLOR=#EF2929]3777[/color][/b]
101         13             4998             101         13             4998

102          5             2667             102          5             2667  
102          8             4998             102          8             4998 
102         27             8772             102         27             8772 

103         22             3771             103         22             3771
103         13             4998             103         13             4998
etc....                                     etc....

The 'glitch' is in this section of code...

SELECT Min(Set) AS MinSet, CountOfValue, Value
FROM tbl_Original
GROUP BY CountOfValue, Value
ORDER BY 1, 3

The change (3771 to 3777) affects only 1 record in Set 101. So, the UNCHANGED record in Set 101 is still being grouped with the other records having CountOfValue = 13 AND Value = 4998. The actual output of the SQL fragment listed above is shown (below left), but the desired output is shown (below right).

[b]Actual output of SQL fragment              Desired output of SQL fragment 
                       
MinSet	CountOfValue	Value               MinSet	CountOfValue	Value[/b]
100	   22	         3771                100	   22	         3771
100	   13	         4998                100	   13	         4998
[b]101	   22	         3777                101	   22	         3777[/b]
102	    5	         2667                [b]101	   13	         4998[/b]
102	    8	         4998                102	    5	         2667
102	   27	         8772                102	    8	         4998
104	   13	         3771                102	   27	         8772                
106	   10	         2667                104	   13	         3771                
106	    7	         8772                106	   10	         2667               
106	   12	         9911                106	    7	         8772                
                                             106	   12	         9911

So, ANY change to Set 101 renders Set 101 (all of its records) as being different. After this fix, everything else would work flawlessly! Any clues or hints?

Much thanks, Vicky C.