Crosstab leaves Blank fields

  • Thread starter Thread starter Cydney
  • Start date Start date
C

Cydney

I've created a Crosstab Query like this:

TRANSFORM First(Query1.ColE) AS FirstColE
SELECT Query1.ColA, Query1.ColB
FROM Query1
GROUP BY Query1.ColA, Query1.ColB
ORDER BY Query1.ColB, "COL" & Format(DCount("*","Query1","ColE<='" & [ColE]
& "'"),"000")
PIVOT "COL" & Format(DCount("*","Query1","SortOrd<='" & [SortOrd] &
"'"),"000");

Everythiing shows up great. My problem is with ColE. The "column" field --
Although it displays correct information, I need to get the "gaps" out of
there. So if the 2nd row doesn't have all the ColE information that the 1st
row has, I need to display them consecutively anyway.

NOT Like this:
ColA ColB COL017 COL034 COL036 COL050
somedata1 moredata Resource Data1 Resource Data2
somedata2 moredata Resource Data1 Resource Data2
somedata3 moredata Resource Data1 Resource Data2 Resource Data3 Resource Data4
somedata4 moredata Resource Data1
somedata5 moredata Resource Data2 Resource Data4
somedata6 moredata Resource Data3
somedata7 moredata Resource Data1 Resource Data2 Resource Data3
somedata8 moredata Resource Data1 Resource Data2

But LIKE This instead:
ColA ColB ResourceCOL1 ResourceCOL2 ResourceCOL3 ResourceCOL4
somedata1 moredata Resource Data1 Resource Data2
somedata2 moredata Resource Data1 Resource Data2
somedata3 moredata Resource Data1 Resource Data2 Resource Data3 Resource Data4
somedata4 moredata Resource Data1
somedata5 moredata Resource Data2 Resource Data4
somedata6 moredata Resource Data3
somedata7 moredata Resource Data1 Resource Data2 Resource Data3
somedata8 moredata Resource Data1 Resource Data2

Of course, when I define the column headings as ResourceCol1 (etc.), the
data goes away...
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson
 
The only way that I know to handle this is to use a ranking query to identify
the order for the values. And then use that to build the crosstab.

Something like the following

SELECT A.ColA, A.ColB, A.ColE, 1+Count(B.SortOrd) as ThePosition
FROM query1 as A LEFT JOIN Query1 as B
ON A.ColA =B.ColA
AND A.ColB = B.ColB
AND A.SortOrd < B.SortOrd
GROUP BY A.ColA, A.ColB, A.ColE,

Then your crosstab becomes something like the following using the saved
ranking query.

TRANSFORM First(ColE)
SELECT ColA, ColB
FROM qRankingQuery
GROUP BY ColA, ColB
PIVOT "COL" & Format(ThePosition,"000")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
That is definitely the answer. I hadn't remembered using Ranking to identify
their order. Makes sense. Thank you so much!
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson


John Spencer said:
The only way that I know to handle this is to use a ranking query to identify
the order for the values. And then use that to build the crosstab.

Something like the following

SELECT A.ColA, A.ColB, A.ColE, 1+Count(B.SortOrd) as ThePosition
FROM query1 as A LEFT JOIN Query1 as B
ON A.ColA =B.ColA
AND A.ColB = B.ColB
AND A.SortOrd < B.SortOrd
GROUP BY A.ColA, A.ColB, A.ColE,

Then your crosstab becomes something like the following using the saved
ranking query.

TRANSFORM First(ColE)
SELECT ColA, ColB
FROM qRankingQuery
GROUP BY ColA, ColB
PIVOT "COL" & Format(ThePosition,"000")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I've created a Crosstab Query like this:

TRANSFORM First(Query1.ColE) AS FirstColE
SELECT Query1.ColA, Query1.ColB
FROM Query1
GROUP BY Query1.ColA, Query1.ColB
ORDER BY Query1.ColB, "COL" & Format(DCount("*","Query1","ColE<='" & [ColE]
& "'"),"000")
PIVOT "COL" & Format(DCount("*","Query1","SortOrd<='" & [SortOrd] &
"'"),"000");

Everythiing shows up great. My problem is with ColE. The "column" field --
Although it displays correct information, I need to get the "gaps" out of
there. So if the 2nd row doesn't have all the ColE information that the 1st
row has, I need to display them consecutively anyway.

NOT Like this:
ColA ColB COL017 COL034 COL036 COL050
somedata1 moredata Resource Data1 Resource Data2
somedata2 moredata Resource Data1 Resource Data2
somedata3 moredata Resource Data1 Resource Data2 Resource Data3 Resource Data4
somedata4 moredata Resource Data1
somedata5 moredata Resource Data2 Resource Data4
somedata6 moredata Resource Data3
somedata7 moredata Resource Data1 Resource Data2 Resource Data3
somedata8 moredata Resource Data1 Resource Data2

But LIKE This instead:
ColA ColB ResourceCOL1 ResourceCOL2 ResourceCOL3 ResourceCOL4
somedata1 moredata Resource Data1 Resource Data2
somedata2 moredata Resource Data1 Resource Data2
somedata3 moredata Resource Data1 Resource Data2 Resource Data3 Resource Data4
somedata4 moredata Resource Data1
somedata5 moredata Resource Data2 Resource Data4
somedata6 moredata Resource Data3
somedata7 moredata Resource Data1 Resource Data2 Resource Data3
somedata8 moredata Resource Data1 Resource Data2

Of course, when I define the column headings as ResourceCol1 (etc.), the
data goes away...
Click to expand...
Click to expand...
 
Back
Top