Counting populated fields in crosstab

[Adam Carpenter]

Hello,

How can I count the number of columns in a row of a crosstab query that
contain data?

Thanks in anticipation.

Adam

 

[Michel Walsh]

Hi,


The number of rows is equal to the number of groups, which is equal
to the number of distinct values of the fields making the groups. The number
of fields is equal to the number of different distinct values the PIVOT
expression can supply.

SELECT COUNT(*) FROM (SELECT DISTINCT f1, f2, f3 FROM myTable)


assuming the groups are based on f1, f2, f3.


Hoping it may help,
Vanderghast, Access MVP

 

[Rufus T. Firefly]

Use the form:

Transform aggregate(column) as somename
Select ......,count(somename) as [number of columns with data]

Utilities for Sql Server @
www.rac4sql.net

 

[Michel Walsh]

Hi Steve,


The count will occur for each group, and thus, the number could be
different, per record, unless you make no group at all (the result is then
just one record) that is... but that is not allowed in a Jet
crosstab...unless you "fake" it... and since we are at it, let us fake the
aggregate too:


TRANSFORM SUM(1) As toto
SELECT COUNT(toto) As ReadThisField
FROM myTable
GROUP BY 1
PIVOT whateverExpressionToBeReallyUsed





Vanderghast, Access MVP

 

[Rufus T. Firefly]

The count will occur for each group, and thus, the number could be
different, per record, unless you make no group at all (the result is then
just one record) that is... but that is not allowed in a Jet
crosstab...unless you "fake" it... and since we are at it, let us fake the
aggregate too:


TRANSFORM SUM(1) As toto
SELECT COUNT(toto) As ReadThisField
FROM myTable
GROUP BY 1
PIVOT whateverExpressionToBeReallyUsed

Hi Michel,

Kewl!
But the OP wanted the count of the number of columns with data in each row

 

[Michel Walsh]

Hi Steve,


... indeed... I didn't read the OP question like that the first time,
but now that you mentionned it...


Vanderghast, Access MVP