This was for a report wasn't it?
If so, I'd probably just sum them up
in the source of a report textbox.
Untested, but you also can give your
TRANSFORM an alias and use that
alias in a row header calculation...
maybe something like:
TRANSFORM Max(Switch(X.FldName='N', T.Judge,
X.FldName='S', T.Score,
X.FldName='R',T.Rank)) As Alias
SELECT
T.[Pagent ID],
T.[Age Group],
T.[Twirler ID],
SUM(Switch(X.FldName='R', Alias, True, 0) As RSum
FROM tblXtab AS X, tblRecapA AS T
WHERE T.[Sub Event] = 3
GROUP BY T.[Pagent ID], T.[Age Group], T.[Twirler ID]
PIVOT "TJ" & T.JudSeq & X.FldName
In ('TJ1N',
'TJ1S',
'TJ1R',
'TJ2N',
'TJ2S',
'TJ2R',
'TJ3N',
'TJ3S',
'TJ3R');
but I don't think that will work because we also really
need to alias X.FldName to use in the Switch or Access
will think we're trying to work a subquery. I don't see
any immediate way to get that alias w/o introducing
another term to your Group By (which you don't want).
maybe, simply...
TRANSFORM Max(Switch(X.FldName='N', T.Judge,
X.FldName='S', T.Score,
X.FldName='R',T.Rank))
SELECT
T.[Pagent ID],
T.[Age Group],
T.[Twirler ID],
SUM(IIF(X.FldName='R', T.Rank, 0) As RSum
FROM tblXtab AS X, tblRecapA AS T
WHERE T.[Sub Event] = 3
GROUP BY T.[Pagent ID], T.[Age Group], T.[Twirler ID]
PIVOT "TJ" & T.JudSeq & X.FldName
In ('TJ1N',
'TJ1S',
'TJ1R',
'TJ2N',
'TJ2S',
'TJ2R',
'TJ3N',
'TJ3S',
'TJ3R');
I *believe* above would sum *all* Ranks over a group
(which I think is what you wanted?)
Still, doing this in report textbox may be your best bet.
good luck Steve,
gary
Steve S said:
Gary, thanks for the help. I have this working and have adapted the idea
to
two other sources for reports. one last question: How would I sum the
values
TJ3R, TJ2R and TJ3R in this cross tab. Currently I am running a second
query
that calculates the sum but it would be great to eliminate that query.
see
my SQL below
TRANSFORM Max(Switch(X.FldName='N', T.Judge,
X.FldName='S', T.Score,
X.FldName='R',T.Rank))
SELECT T.[Pagent ID], T.[Age Group], T.[Twirler ID]
FROM tblXtab AS X, tblRecapA AS T
WHERE T.[Sub Event] = 3
GROUP BY T.[Pagent ID], T.[Age Group], T.[Twirler ID]
PIVOT "TJ" & T.JudSeq & X.FldName
In ('TJ1N',
'TJ1S',
'TJ1R',
'TJ2N',
'TJ2S',
'TJ2R',
'TJ3N',
'TJ3S',
'TJ3R');
thanks much for all your help.
Gary Walter said:
:
:
Well I think I don't need to 'format' the string but to use something
like
Val(), only this does not seem bo convert a blank to 0 (zero).
Hi Steve,
I could be wrong, but I think that the SWITCH
over text and numbers will cast all numbers to
text for the TRANSFORM result.
TRANSFORM
Max(Switch(X.FldName='Jname', T.Judge,
X.FldName='Score', T.Score,
X.FldName='Rank',T.Rank))
I did not understand why it would be important
when showing results in a report textbox which is
showing a "string" anyway...
But, if you want to show "0" in the JudgexScore/JudgexRank
textboxes on the report, use null-to-zero function -- NZ(..)
in the Control Source of their report textboxes.
for example, the score text box for Judge2
--change Name to "txtJudge2Score"
(so you don't get circular reference)
--change Control Source to
= Nz([Judge2Score],0)
I wish below would simply do what you wanted, but it doesn't
TRANSFORM
Max(Switch(X.FldName='Jname', T.Judge,
X.FldName='Score', Nz(T.Score,0),
X.FldName='Rank', Nz(T.Rank,0)))
nor does
TRANSFORM
Max(Switch(X.FldName='Jname', T.Judge,
X.FldName='Score', Nz(T.Score,'0'),
X.FldName='Rank', Nz(T.Rank,'0')))
It makes sense to me, but it does not appear to work?
Actually...maybe it does make sense I guess...
Where they are "blank," that "group" just did not exist
(will not be evaluated), and the blanks only exist because
another group forced those columns.
If the TRANSFORM was an aggregate over a *single field*,
you could wrap the entire aggregate with NZ( ), but in your
case, this would also give the "missing" JudgexName a "0"
which *might* hamper our report structure...
TRANSFORM Nz(Max(Switch(X.FldName='Jname',T.Judge,
X.FldName='Score',T.Score,
X.FldName='Rank',T.Rank)),0)
qryxtabRpt Event Level AgeGroup Twirler Judge1JName Judge1Score
Judge1Rank Judge2JName Judge2Score Judge2Rank Judge3JName Judge3Score
Judge3Rank Judge4JName Judge4Score Judge4Rank
Solo Advanced 10-12 Mary Black Brown 21.8 3 0 0 0 Jones 63.5 1
Simpson 31.4 2
Solo Advanced 10-12 Sally Smith Brown 34.9 2 Harris 21.6 3 Jones
45.3 1 0 0 0
maybe that would work since
Control Source for "txtJudgexJName" would be
=Max([JudgexJName])
that might work?
good luck,
gary
