Crosstab?

[Pulling Hair Out]

My query data has these columns

Record Number C1 C2 C3
1153 1 2 3
1154 2 3 1
1155 1 2 2

I want to count (using record number) the 1's, 2's, 3's, for each C1, C2, C3
like this:

C1 C2 C3

1 2 0 1
2 1 2 1
3 0 1 1

I tried a crosstab query but I couldn't get it to work. Any suggestions?

 

[Douglas J. Steele]

SELECT 1 AS ColumnValue,
Sum(IIf([C1] = 1, 1, 0) AS C1Count1,
Sum(IIf([C2] = 1, 1, 0) AS C2Count1,
Sum(IIf([C3] = 1, 1, 0) AS C3Count1
FROM MyTable
UNION
SELECT 2 AS ColumnValue,
Sum(IIf([C1] = 2, 1, 0) AS C1Count2,
Sum(IIf([C2] = 2, 1, 0) AS C2Count2,
Sum(IIf([C3] = 2, 1, 0) AS C3Count2
FROM MyTable
UNION
SELECT 3 AS ColumnValue,
Sum(IIf([C1] = 3, 1, 0) AS C1Count3,
Sum(IIf([C2] = 3, 1, 0) AS C2Count3,
Sum(IIf([C3] = 3, 1, 0) AS C3Count3
FROM MyTable

 

[KARL DEWEY]

Try this --
qryArrange --
SELECT [query data].[C1] AS [ColumnData], "C1" AS ColumnNUM, [Record Number]
FROM [Query data]
UNION SELECT [query data].[C2] AS [ColumnData], "C2" AS ColumnNUM, [Record
Number]
FROM [Query data]
UNION SELECT [query data].[C3] AS [ColumnData], "C3" AS ColumnNUM, [Record
Number]
FROM [Query data];

TRANSFORM Nz(Count(qryArrange.[ColumnData]),0) AS CountOfColumnData
SELECT qryArrange.ColumnData
FROM qryArrange
GROUP BY qryArrange.ColumnData
PIVOT qryArrange.ColumnNUM;

 

[vanderghast]

You already have two solutions, here a third one, implying the use of a
table iotas, one field, iota, with values from 1 to, say, 10 (or, at least
up to the maximum count).

SELECT iota,
(SELECT COUNT(*) FROM xtab WHERE xtab.C1=iotas.iota) AS inC1,
(SELECT COUNT(*) FROM xtab WHERE xtab.C2=iotas.iota) AS inC2,
(SELECT COUNT(*) FROM xtab WHERE xtab.C3=iotas.iota) AS inC3
FROM iotas



should do, assuming you have only three columns to consider from the
crosstab, C1, C2 and C3.


It could be a little bit slow too, at execution time.



Vanderghast, Access MVP

 

[Pulling Hair Out]

Thank you very much...this is just what i was looking for.

Try this --
qryArrange --
SELECT [query data].[C1] AS [ColumnData], "C1" AS ColumnNUM, [Record Number]
FROM [Query data]
UNION SELECT [query data].[C2] AS [ColumnData], "C2" AS ColumnNUM, [Record
Number]
FROM [Query data]
UNION SELECT [query data].[C3] AS [ColumnData], "C3" AS ColumnNUM, [Record
Number]
FROM [Query data];

TRANSFORM Nz(Count(qryArrange.[ColumnData]),0) AS CountOfColumnData
SELECT qryArrange.ColumnData
FROM qryArrange
GROUP BY qryArrange.ColumnData
PIVOT qryArrange.ColumnNUM;

--
Build a little, test a little.

My query data has these columns

Record Number C1 C2 C3
1153 1 2 3
1154 2 3 1
1155 1 2 2

I want to count (using record number) the 1's, 2's, 3's, for each C1, C2, C3
like this:

C1 C2 C3

1 2 0 1
2 1 2 1
3 0 1 1

I tried a crosstab query but I couldn't get it to work. Any suggestions?