Hi,
TRANSFORM COUNT(whatever) As theValue
SELECT ...
FROM somewhere
GROUP BY ...
PIVOT ...
The TRANSFORMed expression is what appears under the new columns. If you
need to refer to it, in the SELECT clause, you HAVE TO alias it.
Since the query is otherwise a TOTAL query, every expression should be
either GROUP or aggregated. The transformed value is NOT an exception, it
cannot appear in the GROUP, so, it should be aggregated again, but
HORIZONTALLY this time (appearing as if it was horizontally, since the
crosstab denormalize). As example, COUNT(theValue) would count the number of
not null results, horizontally, for the given group, or, if you prefer the
number of PIVOT expressions that supply a not-null value, for that group,
or, if you really prefer, a DISTINCT COUNT, by group. As a simpler example,
SUM(theValue) will produce an horizontal sum of the transformed values, and
so on.
So, in the SELECT, you cannot say
SELECT ... , iif( theValue ... )
since theValue is not aggregated. If you aggregate it, it would supply just
one extra column (not one per PIVOT expression, just like, again SELECT ...,
SUM(theValue), supplies an HORIZONTAL summation.
So, it "seems" to me that what you look for is an iif, but inside the
TRANSFORM clause:
TRANSFORM iif( COUNT(*) < field1, field1, field1*field2) As theIifValue
SELECT ...
FROM ...
GROUP BY ...
PIVOT ...
That won't work either because you compare aggregate with
non-aggregated-non-group expression-non-constant. In fact, COUNT(*)
represent the COUNT of records that are in the given GROUP and that are in
the given PIVOT result: it is the intersection of the "row" (group) and
"column" (Pivot). So, COUNT(*) counts the number of records that belong to
BOTH. That's fine, assume we have 6 such records, COUNT(*)=6, such as if
GROUP is about type="T Shirt" and if PIVOT is about Color = 'red', then, we
have originally, 6 records speaking of Red T-Shirt. For those 6 records,
what is "Field1"? What record, from the 6 we have, will supply the Field1
value? If the answer is "any", then, use LAST:
TRANSFORM iif( COUNT(*) < LAST(field1), LAST(field1),
LAST(field1)*LAST(field2) ) As theIifValue
SELECT ...
FROM ...
GROUP BY type
PIVOT color
If you can't get an answer, if you can't find an aggregate, and if the field
is not a candidate for a group, you are doomed. In fact, if the transformed
value depends on a neither grouped neither aggregated field/expression, the
query won't run. The "value" has to be aggregate since MANY records may be
involved (at a given intersection of row and column) but there is just ONE
"cell", so, just one value should be pumped out of the TRANSFORM, and to be
sure just one does, it should be based on aggregate (or grouped) fields.
From that discussion, you should see that *I* don't really see what you
mean by a COUNT being compared with a field, Field1. As a rule, aggregate
should compare themselves with their pars (or constants, or group
"representative").
Hoping it may help,
Vanderghast, Access MVP
Johnny Access said:
Well a couple months ago I played around with the query
wizard and learned crosstab queries. They have been great
and I have been using them for capacity stuff. Well now I
am trying more complicated stuff it doesnt seem to like
it. I would like to use the count and sum values along
with some IIF statements. Currently I have a count of a
field for the value. But I would to do two different
things depending on what value is returned. Example - If
the count value returned is greater than say [field1] then
multiply [field1]*[field2]anything else just use
[field1]. I have used the IIF plenty of times and now the
crosstabs as well. But never together. So is it possible
to use an function similar to the one above in a crosstab?
