A good crosstab query reference

[Carl Rapson]

I don't know about others, but I continue to have a lot of questions about
crosstab queries. I look at a lot of examples, but they are usually specific
and narrow in scope, and I have trouble extrapolating such samples into new
applications (particularly complex ones). Is there a good generic reference
for crosstab queries? One that goes into some detail on the theory behind
them, as well as the construction of the query itself (the TRANSFORM and
PIVOT clauses, for example)? One that treats generically such concepts as
how to include columns with no data, or how to construct columns more
complex than the usual month names. I know I could post specific questions
here and try to get specific answers, but that's very tedious (as well as
limited in scope) and I would like to learn more of the theory behind
crosstab queries so that I can (hopefully) figure out how things work and
use that knowledge in future applications.

Every Access reference book I have consulted either ignores crosstab queries
completely, or only mentions them in passing. This seems odd considering how
important crosstab queries are. Where can I find such a good, rigorous
reference?

Thanks for any information,

Carl Rapson

 

[Michel Walsh]

Hi,


The PIVOT accepts almost anything, even a user defined function, if you
want.

PIVOT "h" & Rnd( fieldName )


as example, if that can make sense, will generate a random name field,
preceded by the letter h. Don't try it unless you have a very small number
of records, you are still limited to 255 fields, in all. If the pivot
expression evaluates to NULL, the field so created is labeled <> . If you
are not interested in the generated fields at all, you can use:

PIVOT expression IN (Null)


Indeed, you can add a IN list, then all names in the list will generate a
field, but only those fields would be generated.


TRANSFORM is what goes in the "cell" at the intersection of the created
field, horizontally, and the GROUP, vertically. It has to be aggregated (ie,
COUNT, SUM, LAST, MIN, MAX, ... ). If you ALIAS the expression, then that
expression can be re-used again, in the SELECT clause, under another
aggregate:

TRANSFORM SUM(Amount) As SumByAmount
SELECT SUM(SumByAmount) As TotalHorizontalSum
FROM ...
GROUP BY ClientID
PIVOT Format( BillingDate, "yyyy-mm" )



You cannot supply a HAVING clause, but otherwise, the query should respect
the rules of a TOTAL query.

Another example is then to generate a random value, for each pivot. A
solution is:

TRANSFORM MAX( Rnd( numericalPivot ))
SELECT null
FROM ...
GROUP BY myGroup
PIVOT 'F' & NumericalPivot


You need the MAX to satisfy the TOTAL query requirement: a selected field
(include the ones in the transform) should appear in the group by, be
aggregated, or be a combination of the previous two. You could have use MIN,
or LAST, instead of MAX, in this case. Note that if, for a given group, the
Pivot value does not exist, a NULL is supplied in the CELL.





If you need a complex expression in the transform, use a Dxxx rather than a
sub-query. To compute a percentage, as example, since the % has to be in
each cell, we design the expression in the TRANSFORM segment, NOT in the
SELECT segment:

TRANSFORM COUNT(*) / DCOUNT("*", "clients") As Percentage
SELECT ...
FROM clients
GROUP BY SalesManID
PIVOT City & Year( BillingDate )




To replace NULL, in cells, use NZ( ) around the expression, in the TRANSFORM
clause, not in the SELECT clause, since it is the TRANSFORM that supplies
the value for the "cell". I repeat that a NULL will appear in a CELL if
there is no record in the original table, for the given row (group) that
produces the column (Pivot expression).





A recapitulative summary example, also a weird way to COUNT DISTINCT, by
group, under Jet, is to use:

TRANSFORM COUNT(*) As theCell
SELECT COUNT(theCell) As DistinctCount
FROM somewhere
GROUP BY whatever
PIVOT FieldToCountDistinctValues IN(Null)

Indeed, observe that if there is no record, for a group, under the created
field, a NULL is returned in the theCell. COUNTing these theCell, for a
given group, horizontally, returns the number of not null values. Finally,
you are not interested in the created fields, just in the distinct count of
values for each group, so you used IN( NULL ).


And we didn't use join. Crosstabs can become very versatile when combined
with join, but if you ever use a parameter, you are OBLIGED to declare the
parameter data type. That rule applies even if the parameter is used in an
embedded query! Syntax it like:


PARAMETERS www LONG;
TRANSFORM ...
WHERE field1 = www



Hoping it may help,
Vanderghast, Access MVP

 

[Carl Rapson]

Michel,

Thanks, I appreciate the in-depth information about TRANSFORM and PIVOT.
This is certainly more information than I've seen in any reference book. I
will need to go over your information carefully, but I'm sure it will help
me once I can apply it to my particular applications.

Carl