Counting populated fields in crosstab

  • Thread starter Thread starter Adam Carpenter
  • Start date Start date
A

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
 
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
 
Use the form:

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

Utilities for Sql Server @
www.rac4sql.net
 
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
 
Michel Walsh" said:
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
Click to expand...

Hi Michel,

Kewl!
But the OP wanted the count of the number of columns with data in each row:)
 
Hi Steve,


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


Vanderghast, Access MVP
 
Back
Top