Cell elimination (hiding) of a crosstab query 1

[GKIL67]

Hello all, do you know if there is a simple way to
eliminate/hide the empty cells in a crosstab query?

Kindly see the attached picture, I want to get from the original Display 1 to Display 2.

The column headings (1301, 1300, 1299...) is only an
increasing counter... maybe there is a way to "cheat" it
so that the relevant data numbers underneath are next to each other?

This is the code of the original Display 1:

TRANSFORM First(skipPat45Rows.SKIPS) AS FirstOfSKIPS
SELECT [5&20].FIVE, Count(skipPat45Rows.SKIPS) AS TotalSKIPS
FROM [5&20] LEFT JOIN skipPat45Rows ON [5&20].FIVE = skipPat45Rows.BALL
GROUP BY [5&20].FIVE
ORDER BY [5&20].FIVE, skipPat45Rows.DrawNo DESC 
PIVOT skipPat45Rows.DrawNo;

Thank you in advance for any valuable feedback.

 

[dhookom]

I don't understand why some columns are displayed and others aren't. Some columns without values are displayed in the second image while some columns with values are not. It makes no sense to me.

Have you tried an inner join rather than LEFT JOIN?

Duane
Hook'D on Access
MS Access MVP

 

[GKIL67]

My mistake, the columns without any values should not be displayed (I did it by hand by copy/pasting the values in XL).
No... left join is not the issue.

Let me put it in another way: is there any way we can "forget"
about the column headings and find a way to shift the data
values to the left by eliminating/hiding the empty cells?
[FIVE] is a lottery ball from 1-45.
[Column headings] is the number of the drawing (unique and increasing).
[Data values] is the sum of times (drawings) a [FIVE] did not
show up (skips) between the drawings.
[TotalSkips]is the count of all [Data values].

I've attached an mdb, the query HideEmptyCells is my problem!

Thank you!

 

[dhookom]

Sorry I can't/won't open files from work. Either post your data and specs or wait for someone else to provide some support.

Crosstab Column Headings are not generated where there are no records containing the values. You should be able to filter out blanks prior to or during the crosstab query.

Duane
Hook'D on Access
MS Access MVP

 

[GKIL67]

I understand and respect your any reasons for that!

Your point is clear, however that's the issue. For some numbers of [FIVE] there is column data and for some others there isn't any. How can we "ignore"/trick the column heading
so that if there is data then to "move" it to the left,
one next to the other?

This is the basic Table, tblPatSkips:

============
SELECT tblPatSkips.BALL, tblPatSkips.SKIPS, tblPatSkips.DrawNo
FROM tblPatSkips
WHERE (((tblPatSkips.DrawNo)>1290))
ORDER BY tblPatSkips.BALL;
============
BALL	SKIPS	DrawNo
1	12	1291
2	5	1292
2	5	1298
4	3	1300
4	6	1293
5	8	1295
6	4	1299
8	7	1296
8	0	1295
11	4	1293
11	3	1298
11	1	1302
12	4	1299
13	0	1303
13	3	1294
13	4	1298
13	2	1291
14	6	1297
14	4	1292
15	8	1295
17	10	1293
21	0	1299
21	0	1298
21	0	1297
21	1	1295
21	3	1300
22	1	1302
23	3	1300
23	3	1296
23	3	1292
24	3	1292
24	7	1296
27	2	1301
28	4	1299
28	2	1296
28	1	1294
29	6	1297
31	0	1303
32	2	1301
32	4	1296
32	1	1294
32	0	1293
32	0	1292
33	1	1302
34	0	1299
34	4	1294
34	0	1301
34	1	1302
34	0	1300
35	2	1301
35	3	1297
36	6	1297
36	3	1293
37	8	1295
38	0	1303
38	0	1302
38	10	1291
39	0	1303
39	4	1298
40	9	1294
40	2	1291
43	2	1301
43	0	1300
44	11	1291
44	0	1303

The attached picture below displays the output of this,
called Original Display:

=============
TRANSFORM First(skipPat45Rows.SKIPS) AS FirstOfSKIPS
SELECT [5&20].FIVE, Count(skipPat45Rows.SKIPS) AS TotalSKIPS
FROM [5&20] LEFT JOIN skipPat45Rows ON [5&20].FIVE = skipPat45Rows.BALL
GROUP BY [5&20].FIVE
ORDER BY [5&20].FIVE, skipPat45Rows.DrawNo DESC 
PIVOT skipPat45Rows.DrawNo;
=============

And the Desired Display of the attached picture is what we
want to transform to - Note that the role of the Column
Headings is not of any value, they are just there...

Any ideas?

 

[GKIL67]

Hello all, I can't believe there hasn't been a confident answer so far. Is it because my question is not clear or is it because my inquiry is NOT doable in a crosstab query?

Maybe somebody, with enough experience, could take a look and
lead the closing of this thread with a proper technical
resolution?

Thank you in advance!

 

[dhookom]

How does your

============
SELECT tblPatSkips.BALL, tblPatSkips.SKIPS, tblPatSkips.DrawNo
FROM tblPatSkips
WHERE (((tblPatSkips.DrawNo)>1290))
ORDER BY tblPatSkips.BALL;
============
BALL    SKIPS    DrawNo
1    12    1291
2    5    1292
...

Relate to

=============
TRANSFORM First(skipPat45Rows.SKIPS) AS FirstOfSKIPS
SELECT [5&20].FIVE, Count(skipPat45Rows.SKIPS) AS TotalSKIPS
FROM [5&20] LEFT JOIN skipPat45Rows ON [5&20].FIVE = skipPat45Rows.BALL
GROUP BY [5&20].FIVE
ORDER BY [5&20].FIVE, skipPat45Rows.DrawNo DESC 
PIVOT skipPat45Rows.DrawNo;
=============

There are to other table/queries and unknown fields.

To be honest, I was confused by the above, didn't care to or couldn't view the shared files from work, and saw an unconventional table/query name like [5&20].


Duane
Hook'D on Access
MS Access MVP

 

[GKIL67]

The use of the table [5&20] is only to force the appearing of
all numbers (field [FIVE] = field [BALL]), from 1-45.
To make it more simple we can exclude it, so we get the:

TRANSFORM First(skipPat45Rows.SKIPS) AS FirstOfSKIPS
SELECT skipPat45Rows.BALL, Count(skipPat45Rows.SKIPS) AS TotalSKIPS
FROM skipPat45Rows
GROUP BY skipPat45Rows.BALL
ORDER BY skipPat45Rows.BALL, skipPat45Rows.DrawNo DESC 
PIVOT skipPat45Rows.DrawNo;

which produces the same result, but from the column [BALL]
the i.e. 3 is missing... it's OK!

My question remains how to shift the data of [SKIPS]to the
left (ignore the column they are suppose to appear and move the data to the 1st left column) so instead of having 12 under DrawNo 1291 to display it under DrawNo 1303 etc.
i.e. Desired output:

FIVE	TotalSKIPS	1303	1302
1	     1		    12 (move it here from 1291)
2	     2	          5	 5 (move them here from 1298, 1292)
4	     2	          3	 6 (move them here from 1300, 1293)

Do you think is doable?
Thanks!

 

[dhookom]

You still haven't told us what skipPat45Rows is.

If I understand correctly, try create a ranking query [qrnkSkips]:

SELECT skipPat45Rows.BALL, skipPat45Rows.SKIPS, skipPat45Rows.DrawNo, Count(skipPat45Rows.DrawNo) AS ColHead
FROM skipPat45Rows AS skipPat45Rows_1 INNER JOIN skipPat45Rows ON skipPat45Rows_1.BALL = skipPat45Rows.BALL
WHERE (((skipPat45Rows.DrawNo)>=[skipPat45Rows_1].[DrawNo]))
GROUP BY skipPat45Rows.BALL, skipPat45Rows.SKIPS, skipPat45Rows.DrawNo
ORDER BY skipPat45Rows.BALL;

Then create a crosstab

TRANSFORM First(qrnkSkips.SKIPS) AS FirstOfSKIPS
SELECT qrnkSkips.BALL, Count(qrnkSkips.SKIPS) AS TotalSkips
FROM qrnkSkips
GROUP BY qrnkSkips.BALL
PIVOT qrnkSkips.ColHead;

[tt]
BALL TotalSkips 1 2 3 4 5
1 1 12
2 2 5 5
4 2 6 3
5 1 8
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[GKIL67]

Well, skipPat45Rows shows for a lottery game each BALL
how many SKIPS passed before the BALL appeared last time and DrawNo is the increasing counter of all the drawings.
For example,
BALL no 1 has SKIPped 12 drawings and last appeared on DrawNo 1291.

BALL no 2 has SKIPped 5 drawings, last appeared on DrawNo 1298 and before that SKIPped another 5 drawings and appeared on DrawNo 1292.

So the "hiding" of the empty cells is doable,
thanks to your suggestion for a ranking query.

Thank you so much!