If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Do I need a Crosstab Query (html)
Maybe not needed, but you can also alias the PIVOT
in SELECT clause w/o introducing more groups!!! and use the TRANSFORM alias with that PIVOT alias in your Switch stmt for summing... TRANSFORM Max(Switch(X.FldName='N', T.Judge, X.FldName='S', T.Score, X.FldName='R',T.Rank)) AS TAlias SELECT T.[Pagent ID], T.[Age Group], T.[Twirler ID], "TJ" & T.JudSeq & X.FldName AS PAlias, SUM(Switch(PAlias IN ('TJ1R', 'TJ2R', 'TJ3R'), TAlias, 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'); I'm pretty sure above would work. good luck, gary "Gary Walter" wrote: 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" wrote: 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" wrote: "Gary Walter" wrote: "Steve S" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|