Do I need a Crosstab Query

[Steve S]

I have a report (based on a simple select query)in the following format:

Athlete Name
JudgeA name Score Rank
JudgeB name Score Rank
JudgeC name Score Rank

SumScore

I need to change the report format to:


JudgeA JudgeB JudgeC
AthleteName Score Rank Score Rank Score Rank SumScore


Is there a way to change the report to display the needed format or do I
need a new report based on a crosstab query? Also part of the problem is
that some events have one Judge, some 2 judges and others up to a max of 4
judges.
Any and all help is appreciated

 

[Duane Hookom]

I would create a main report based on a query of all athletes like:
SELECT Athlete
FROM simplequery
GROUP BY Athlete;
Then create a multiple column subreport based on your simple query. Use the
link master child properties to limit the subreport to the appropriate
Athlete.

 

[Gary Walter]

I have a report (based on a simple select query)in the following format:

Athlete Name
JudgeA name Score Rank
JudgeB name Score Rank
JudgeC name Score Rank

SumScore

I need to change the report format to:


JudgeA JudgeB JudgeC
AthleteName Score Rank Score Rank Score Rank SumScore


Is there a way to change the report to display the needed format or do I
need a new report based on a crosstab query? Also part of the problem is
that some events have one Judge, some 2 judges and others up to a max of
4
judges.

Hi Steve,

In addition to Duane's sage help, I wonder
if we know enough details.

Are Score and Rank separate fields in a table?

Where is "Event" in your example?

Could you give table structure(s),
i,e,. table/field names and field types,
some sample data that might cover diff in events,
how you would like the sample data to
appear in your new report,
and the SQL for your current report query?

I don't know...maybe I just ate too much turkey...
but it just feels like in an attempt to be brief, all the
details needed to best assist you have been "yada-yada-yadaed."

To me, it looks like you may need to create one of
Duane's "relative PIVOTS" for varying # of judges
(do you need actual judge names, or only that a
specific judge results appear under the same column)

and if you want 2 values for each "relative Judge,"
("Score" and "Rank") you may need to use the method
that Duane has referenced before from

http://www.tek-tips.com/faqs.cfm?fid=4524

where you create a table "tblXtabColumns"
with the single field [FldName] with 2 records

"Score"
"Rank"

and include this table via Cartesian join in your xtab.

good luck,

gary

 

[Steve S]

Hi Steve,

In addition to Duane's sage help, I wonder
if we know enough details.

Are Score and Rank separate fields in a table?
-- Two seperate fields --
Where is "Event" in your example?
-- See modified report examples I have added thes fields --

Could you give table structure(s),
i,e,. table/field names and field types,
some sample data that might cover diff in events,
how you would like the sample data to
appear in your new report,
and the SQL for your current report query?

____________________________________________________
The record source for the report is a query that selects 6 data fields
from 4 tables

Event from Fees.Event Text
Level from Fees.Level Text
AgeGroup from Entries.AgeGroup Text
Twirler from Twirlers.LastName Text
Score from Scores.Score Single
Rank from Scores.Rank Byte
Judge from Twirlers_1.LastName Text


Sample Data
What I have now:

Solo
Advanced 10-12

Sally Smith
Harris 21.6 3
Jones 45.3 1
Brown 34.9 2

Mary Black
Harris 31.4 2
Jones 63.5 1
Brown 21.8 3

What I need

Solo
Advanced 10-12
Harris Jones Smith
Sally Smith 21.6 3 45.3 1 39.9 2
Mary Black 31.4 2 63.5 1 21.8 3



the SQL for the current query (Record source)

SELECT Fees.Event, Fees.Level, Entries.[Age Group], Twirlers.[Last Name] AS
Twirler, Scores.Score, Scores.Rank, Twirlers_1.[Last Name] AS Judge
FROM Twirlers RIGHT JOIN ((Fees INNER JOIN [Pagent Links] ON Fees.[Fee ID] =
[Pagent Links].[Fee ID]) INNER JOIN ((Entries LEFT JOIN Scores ON
Entries.[Entry ID] = Scores.[Entry ID]) LEFT JOIN Twirlers AS Twirlers_1 ON
Scores.Judge = Twirlers_1.[Twirler ID]) ON Fees.[Fee ID] = Entries.[Fee ID])
ON Twirlers.[Twirler ID] = Entries.[Twirler ID]
WHERE (((Fees.[Contest ID])=[Forms]![Menu2]![Contest ID]));


Hope this helps
____________________________________________________________________

I don't know...maybe I just ate too much turkey...
but it just feels like in an attempt to be brief, all the
details needed to best assist you have been "yada-yada-yadaed."

To me, it looks like you may need to create one of
Duane's "relative PIVOTS" for varying # of judges
(do you need actual judge names, or only that a
specific judge results appear under the same column)

and if you want 2 values for each "relative Judge,"
("Score" and "Rank") you may need to use the method
that Duane has referenced before from

http://www.tek-tips.com/faqs.cfm?fid=4524

where you create a table "tblXtabColumns"
with the single field [FldName] with 2 records

"Score"
"Rank"

and include this table via Cartesian join in your xtab.

good luck,

gary

 

[Steve S]

Also what is a "relative PIVOT"?

I have a report (based on a simple select query)in the following format:

Athlete Name
JudgeA name Score Rank
JudgeB name Score Rank
JudgeC name Score Rank

SumScore

I need to change the report format to:


JudgeA JudgeB JudgeC
AthleteName Score Rank Score Rank Score Rank SumScore


Is there a way to change the report to display the needed format or do I
need a new report based on a crosstab query? Also part of the problem is
that some events have one Judge, some 2 judges and others up to a max of
4
judges.

Hi Steve,

In addition to Duane's sage help, I wonder
if we know enough details.

Are Score and Rank separate fields in a table?

Where is "Event" in your example?

Could you give table structure(s),
i,e,. table/field names and field types,
some sample data that might cover diff in events,
how you would like the sample data to
appear in your new report,
and the SQL for your current report query?

I don't know...maybe I just ate too much turkey...
but it just feels like in an attempt to be brief, all the
details needed to best assist you have been "yada-yada-yadaed."

To me, it looks like you may need to create one of
Duane's "relative PIVOTS" for varying # of judges
(do you need actual judge names, or only that a
specific judge results appear under the same column)

and if you want 2 values for each "relative Judge,"
("Score" and "Rank") you may need to use the method
that Duane has referenced before from

http://www.tek-tips.com/faqs.cfm?fid=4524

where you create a table "tblXtabColumns"
with the single field [FldName] with 2 records

"Score"
"Rank"

and include this table via Cartesian join in your xtab.

good luck,

gary

 

[Gary Walter]

:

____________________________________________________
The record source for the report is a query that selects 6 data fields
from 4 tables

Event from Fees.Event Text
Level from Fees.Level Text
AgeGroup from Entries.AgeGroup Text
Twirler from Twirlers.LastName Text
Score from Scores.Score Single
Rank from Scores.Rank Byte
Judge from Twirlers_1.LastName Text


Sample Data
What I have now:

Solo
Advanced 10-12

Sally Smith
Harris 21.6 3
Jones 45.3 1
Brown 34.9 2

Mary Black
Harris 31.4 2
Jones 63.5 1
Brown 21.8 3

What I need

Solo
Advanced 10-12
Harris Jones Smith
Sally Smith 21.6 3 45.3 1 39.9 2
Mary Black 31.4 2 63.5 1 21.8 3



the SQL for the current query (Record source)

SELECT Fees.Event, Fees.Level, Entries.[Age Group], Twirlers.[Last Name]
AS
Twirler, Scores.Score, Scores.Rank, Twirlers_1.[Last Name] AS Judge
FROM Twirlers RIGHT JOIN ((Fees INNER JOIN [Pagent Links] ON Fees.[Fee ID]
=
[Pagent Links].[Fee ID]) INNER JOIN ((Entries LEFT JOIN Scores ON
Entries.[Entry ID] = Scores.[Entry ID]) LEFT JOIN Twirlers AS Twirlers_1
ON
Scores.Judge = Twirlers_1.[Twirler ID]) ON Fees.[Fee ID] = Entries.[Fee
ID])
ON Twirlers.[Twirler ID] = Entries.[Twirler ID]
WHERE (((Fees.[Contest ID])=[Forms]![Menu2]![Contest ID]));


Hope this helps
____________________________________________________________________

Whew Steve,

I believe Chandler Bing quote went like:

"can....open.....worms........everywhere....."

I cannot see a way to easily get you to a crosstab query
*that you can use in a report*....sorry...

remember, the xtab would be creating these extra
columns "out of thin air," but a report wants a constant
field name for those columns.

initially I thought we might could come up with relative names:

Judge1Score Judge1Rank Judge2Score Judge2Rank ....

which I believe is doable (with difficulty)...
but I don't immediately see how to put judge name
over its Score/Rank in report...

how difficult? (which may not work...)

for each record in xtab, we would want 3 "relative" columns
for each judge...

Judge1Name Judge1Score Judge1Rank Judge2Name ....

You create a table "tblXtabColumns"
with the single field [FldName] with 3 records

"JName"
"Score"
"Rank"

Turn your query into a make table query
and save results in table "tblReport"

Add one column to tblReport -- a quasi-rank
for each judge in each group -- "JudRank"
type Long.

For now, manually type them in, i.e., for each
record where Harris is judge, set JudRank to 1,
where Jones is judge, set JudRank to 2,
where Brown is judge, set JudRank to 3, etc.

Eventually, we would need to write an update
query that sets these JudRank's within a
Event/Level/[Age Group] group.

So (untested) your report xtab might be:

TRANSFORM
First(
IIF([FldName]='JName',First([Judge]),
IIF([FldName]='Score',First([Score]),
First([Rank]))
)
SELECT
Event.
Level,
[Age Group],
Twirler
FROM tblXtabColumns, tblReport
GROUP BY
Event,
Level,
[Age Group],
Twirler
PIVOT "Judge" & JudRank & [FldName]
IN
('Judge1JName',
'Judge1Score'
'Judge1Rank',
'Judge2JName',
'Judge2Score'
'Judge2Rank',
'Judge3JName',
'Judge3Score'
'Judge3Rank',
'Judge4JName',
'Judge4Score'
'Judge4Rank');

you said max number of judges was 4 right?

this "relative" PIVOT always gives you
the same field names for your report textboxes
to bound to...

Your report "Sorting and Grouping" would have 2 groups

Field/Expression

Event
[Level] & [Age Group]

in the second group header you would have
textboxes for

[Level]
[Age Group]
[Judge1JName]
[Judge2JName]
[Judge3JName]
[Judge4JName]

you can adjust height of this group header
so Level and Age Group appear to be on
one line, and judge names on another...

in Details you would have [Twirler]
and align "JudgexScore and JudgexRank"
under appropriate "JudgexJName"

good luck,

gary

 

[Gary Walter]

Just to test, I started with following "tblReport" (note [AgeGroup] has no
space)

tblReport ID Event Level AgeGroup Twirler Score Rank Judge JudRank
1 Solo Advanced 10-12 Sally Smith 21.6 3 Harris 1
2 Solo Advanced 10-12 Sally Smith 45.3 1 Jones 2
3 Solo Advanced 10-12 Sally Smith 34.9 2 Brown 3
4 Solo Advanced 10-12 Mary Black 31.4 2 Harris 1
5 Solo Advanced 10-12 Mary Black 63.5 1 Jones 2
6 Solo Advanced 10-12 Mary Black 21.8 3 Brown 3



and "tblXtabColumns"

tblXtabColumns FldName
Jname
Score
Rank


and ran this xtab

TRANSFORM
Max(Switch(X.FldName='Jname', T.Judge,
X.FldName='Score', T.Score,
X.FldName='Rank',T.Rank))
SELECT
T.Event,
T.Level,
T.AgeGroup,
T.Twirler
FROM tblXtabColumns AS X, tblReport AS T
GROUP BY
T.Event,
T.Level,
T.AgeGroup,
T.Twirler
PIVOT "Judge" & T.JudRank & X.FldName
In ('Judge1JName',
'Judge1Score',
'Judge1Rank',
'Judge2JName',
'Judge2Score',
'Judge2Rank',
'Judge3JName',
'Judge3Score',
'Judge3Rank',
'Judge4JName',
'Judge4Score',
'Judge4Rank');


and got the following result:

qryxtabRpt Event Level AgeGroup Twirler Judge1JName Judge1Score Judge1Rank
Judge2JName Judge2Score Judge2Rank Judge3JName Judge3Score Judge3Rank
Judge4JName Judge4Score Judge4Rank
Solo Advanced 10-12 Mary Black Harris 31.4 2 Jones 63.5 1 Brown 21.8 3



Solo Advanced 10-12 Sally Smith Harris 21.6 3 Jones 45.3 1 Brown 34.9
2

 

[Gary Walter]

Just to test, I started with following "tblReport"
(note [AgeGroup] has no space)

tblReport ID Event Level AgeGroup Twirler Score Rank Judge JudRank
1 Solo Advanced 10-12 Sally Smith 21.6 3 Harris 1
2 Solo Advanced 10-12 Sally Smith 45.3 1 Jones 2
3 Solo Advanced 10-12 Sally Smith 34.9 2 Brown 3
4 Solo Advanced 10-12 Mary Black 31.4 2 Harris 1
5 Solo Advanced 10-12 Mary Black 63.5 1 Jones 2
6 Solo Advanced 10-12 Mary Black 21.8 3 Brown 3


and "tblXtabColumns"

tblXtabColumns FldName
Jname
Score
Rank

and ran this xtab

TRANSFORM
Max(Switch(X.FldName='Jname', T.Judge,
X.FldName='Score', T.Score,
X.FldName='Rank',T.Rank))
SELECT
T.Event,
T.Level,
T.AgeGroup,
T.Twirler
FROM tblXtabColumns AS X, tblReport AS T
GROUP BY
T.Event,
T.Level,
T.AgeGroup,
T.Twirler
PIVOT "Judge" & T.JudRank & X.FldName
In ('Judge1JName',
'Judge1Score',
'Judge1Rank',
'Judge2JName',
'Judge2Score',
'Judge2Rank',
'Judge3JName',
'Judge3Score',
'Judge3Rank',
'Judge4JName',
'Judge4Score',
'Judge4Rank');


and got the following result:

qryxtabRpt Event Level AgeGroup Twirler Judge1JName Judge1Score Judge1Rank Judge2JName Judge2Score Judge2Rank Judge3JName Judge3Score Judge3Rank Judge4JName Judge4Score Judge4Rank
Solo Advanced 10-12 Mary Black Harris 31.4 2 Jones 63.5 1 Brown 21.8 3



Solo Advanced 10-12 Sally Smith Harris 21.6 3 Jones 45.3 1 Brown 34.9 2

 

[Gary Walter]

btw, if JudRank=0 for all records
and the *same judges* appear for
each Twirler in a Event/Level/AgeGroup...

tblReport ID Event Level AgeGroup Twirler Score Rank Judge JudRank
1 Solo Advanced 10-12 Sally Smith 21.6 3 Harris 0
2 Solo Advanced 10-12 Sally Smith 45.3 1 Jones 0
3 Solo Advanced 10-12 Sally Smith 34.9 2 Brown 0
4 Solo Advanced 10-12 Mary Black 31.4 2 Harris 0
5 Solo Advanced 10-12 Mary Black 63.5 1 Jones 0
6 Solo Advanced 10-12 Mary Black 21.8 3 Brown 0


then following update query will set JudRank

UPDATE
tblReport AS M
INNER JOIN
tblReport AS P
ON
(M.Twirler = P.Twirler)
AND
(M.AgeGroup = P.AgeGroup)
AND
(M.Level = P.Level)
AND
(M.Event = P.Event)
SET M.JudRank = 1+[M].[JudRank]
WHERE
[M].Judge >= [P].[Judge];

producing

tblReport ID Event Level AgeGroup Twirler Score Rank Judge JudRank
1 Solo Advanced 10-12 Sally Smith 21.6 3 Harris 2
2 Solo Advanced 10-12 Sally Smith 45.3 1 Jones 3
3 Solo Advanced 10-12 Sally Smith 34.9 2 Brown 1
4 Solo Advanced 10-12 Mary Black 31.4 2 Harris 2
5 Solo Advanced 10-12 Mary Black 63.5 1 Jones 3
6 Solo Advanced 10-12 Mary Black 21.8 3 Brown 1

 

[Steve S]

Thanks much Gary. sorry to take so long to get back and check for responces
but other things got in the way. I think your last two posts will solve my
problem. You tesated this with a table as initial input but I assume a query
will work?

thanks again.

 

[Gary Walter]

Hi Steve,

Crosstabs like temp tables and that's how
I would attack this report.

You have a filtering query to get the initial data.

Just once, change to make table (say "tblReport") and run.

Then, change to append query and save (say "qryapptblReport").

Add primary key autonumber "ID" field
to table you just created, plus "JudRank"
with Default Value of 0.

In the click event to open your report
'------------------
Dim db AS DAO.Database
Dim strSQL as String

Set db = CurrentDd

'clear temp table
db.Execute "DELETE * FROM tblReport", dbFailOnError

'run filter append query
db.Execute "qryapptblReport", dbFailOnError

'run update JudRank query (say saved as "qryupdJudRank")
db.Execute "qryupdJudRank", dbFailOnError

'open report that is based on saved crosstab query
DoCmd.OpenReport "rptXTab", acPreview

db.Close

'----------------------

That's basically how I would approach this
(except I would probably not depend on
stored queries, but would "spell them out"
into strSQL and execute strSQL instead)

for example....

'update JudRank
strSQL = "UPDATE tblReport AS M INNER JOIN tblReport AS P " _
& "ON (M.Twirler = P.Twirler) AND (M.AgeGroup = P.AgeGroup) " _
& "AND (M.Level = P.Level) AND (M.Event = P.Event) " _
& "SET M.JudRank = 1+[M].[JudRank] " _
& "WHERE [M].Judge >= [P].[Judge];"

db.Execute strSQL, dbFailOnError

good luck,

gary

Thanks much Gary. sorry to take so long to get back and check for
responces
but other things got in the way. I think your last two posts will solve
my
problem. You tesated this with a table as initial input but I assume a
query
will work?

thanks again.

btw, if JudRank=0 for all records
and the *same judges* appear for
each Twirler in a Event/Level/AgeGroup...

tblReport ID Event Level AgeGroup Twirler Score Rank Judge JudRank
1 Solo Advanced 10-12 Sally Smith 21.6 3 Harris 0
2 Solo Advanced 10-12 Sally Smith 45.3 1 Jones 0
3 Solo Advanced 10-12 Sally Smith 34.9 2 Brown 0
4 Solo Advanced 10-12 Mary Black 31.4 2 Harris 0
5 Solo Advanced 10-12 Mary Black 63.5 1 Jones 0
6 Solo Advanced 10-12 Mary Black 21.8 3 Brown 0


then following update query will set JudRank

UPDATE
tblReport AS M
INNER JOIN
tblReport AS P
ON
(M.Twirler = P.Twirler)
AND
(M.AgeGroup = P.AgeGroup)
AND
(M.Level = P.Level)
AND
(M.Event = P.Event)
SET M.JudRank = 1+[M].[JudRank]
WHERE
[M].Judge >= [P].[Judge];

producing

tblReport ID Event Level AgeGroup Twirler Score Rank Judge JudRank
1 Solo Advanced 10-12 Sally Smith 21.6 3 Harris 2
2 Solo Advanced 10-12 Sally Smith 45.3 1 Jones 3
3 Solo Advanced 10-12 Sally Smith 34.9 2 Brown 1
4 Solo Advanced 10-12 Mary Black 31.4 2 Harris 2
5 Solo Advanced 10-12 Mary Black 63.5 1 Jones 3
6 Solo Advanced 10-12 Mary Black 21.8 3 Brown 1

 

[Gary Walter]

Hi Steve,

First, its "Set db = CurrentDb"...

Second, one other advantage of putting
your report data into a temp table means
that you have different strategies for computing
the JudRank -- which for the purpose of the
xtab and report I believe needs each Judge to be
associated with a distinct integer *within an
Event/Level/AgeGroup group*.

For example, what happens if within one of these
groups one twirler has judges Harris/Jones/Brown
while other twirler has Simpson/Jones/Brown?

tblReport Event Level AgeGroup Twirler Score Rank Judge
Solo Advanced 10-12 Sally Smith 21.6 3 Harris
Solo Advanced 10-12 Sally Smith 45.3 1 Jones
Solo Advanced 10-12 Sally Smith 34.9 2 Brown
Solo Advanced 10-12 Mary Black 31.4 2 Simpson
Solo Advanced 10-12 Mary Black 63.5 1 Jones
Solo Advanced 10-12 Mary Black 21.8 3 Brown


By report data being in temp table, for the situation above,
we can create a distinct judges query (say "qryDistinctJudges")

SELECT DISTINCT Judge FROM tblReport;

Then we could use a domain function to get JudRank
in "qryupdJudRank"

UPDATE tblReport SET tblReport.JudRank = DCount("*","qryDistinctJudges","[Judge]<='" & [Judge] & "'");

giving

tblReport ID Event Level AgeGroup Twirler Score Rank Judge JudRank
1 Solo Advanced 10-12 Sally Smith 21.6 3 Harris 2
2 Solo Advanced 10-12 Sally Smith 45.3 1 Jones 3
3 Solo Advanced 10-12 Sally Smith 34.9 2 Brown 1
4 Solo Advanced 10-12 Mary Black 31.4 2 Simpson 4
5 Solo Advanced 10-12 Mary Black 63.5 1 Jones 3
6 Solo Advanced 10-12 Mary Black 21.8 3 Brown 1


so our xtab gives

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


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





in report, Control Source for "txtJudgexJName" would be

=Max([JudgexJName])

Maybe that won't ever happen, or maybe it will be worse?

Also, is it possible you will have more than one
Event/Level/AgeGroup in tblReport?

If so, I imagine then you would change distinct query to

SELECT DISTINCT Event, Level, AgeGroup, Judge
FROM tblReport;

then to get distinct ranks over each group

UPDATE tblReport
SET tblReport.JudRank =
DCount("*","qryDistinctJudges",
"[Judge]<='" & [Judge] & "'
AND
[Event]='" & [Event] & "'
AND
[Level]='" & [Level] & "'
AND
[AgeGroup]='" & [AgeGroup] & "'");

I don't know....you know your data best...

good luck,

gary

Crosstabs like temp tables and that's how
I would attack this report.

You have a filtering query to get the initial data.

Just once, change to make table (say "tblReport") and run.

Then, change to append query and save (say "qryapptblReport").

Add primary key autonumber "ID" field
to table you just created, plus "JudRank"
with Default Value of 0.

In the click event to open your report
'------------------
Dim db AS DAO.Database
Dim strSQL as String

Set db = CurrentDd

'clear temp table
db.Execute "DELETE * FROM tblReport", dbFailOnError

'run filter append query
db.Execute "qryapptblReport", dbFailOnError

'run update JudRank query (say saved as "qryupdJudRank")
db.Execute "qryupdJudRank", dbFailOnError

'open report that is based on saved crosstab query
DoCmd.OpenReport "rptXTab", acPreview

db.Close

'----------------------

That's basically how I would approach this
(except I would probably not depend on
stored queries, but would "spell them out"
into strSQL and execute strSQL instead)

for example....

'update JudRank
strSQL = "UPDATE tblReport AS M INNER JOIN tblReport AS P " _
& "ON (M.Twirler = P.Twirler) AND (M.AgeGroup = P.AgeGroup) " _
& "AND (M.Level = P.Level) AND (M.Event = P.Event) " _
& "SET M.JudRank = 1+[M].[JudRank] " _
& "WHERE [M].Judge >= [P].[Judge];"

db.Execute strSQL, dbFailOnError

good luck,

gary

Thanks much Gary. sorry to take so long to get back and check for
responces
but other things got in the way. I think your last two posts will solve
my
problem. You tesated this with a table as initial input but I assume a
query
will work?

thanks again.

 

[Steve S]

thanks very much for all the help - this project is coming along great. The
problem I have now is that the values in the crosstab under the headings
'JudgeXScore' and 'JudgeXRank' are string values and I need numerical values.
Is there a way to use 'Format()' to change these strings into numbers? I do
not have a great deal of seperience with native SQL, I depend on the query
builder Grid for nost of my querys. when I open your crosstab sample (which
is slick and works great) in QBG It will not let me save changes. so I need
to know to make the change in the SQL screen. thanks again for all your help.

steve

 

[Steve S]

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).

HELP

 

[Gary Walter]

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?

good luck,

gary

 

[Gary Walter]

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

 

[Steve S]

that produces 0's just as you expected but character (string) not numeric 0.

the record source for the report is actually a query with 3 crosstabs (as
you designed for me) and 2 other tables. In that query I can use NZ() to get
numeric values where needed. One crosstab is for Modeling, one for Strut,
and one for Twirling. The overall score is the sum of 25% of Modeling, 35%
of strut and 40% of Twirling. I also have to calculate a ranking within each
event based on the sum of scores for that event.

the final report is so wide it has to be printed landscape on legal paper in
a very small font if there is more than 2 judges.

thanks for your help.
--
Steve S

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

 

[Gary Walter]

Hi Steve,

Can you "think thinner?"

What if you made a table from a crosstab
that had 4 judges.

Go into this table you just made and
change Type of the Score/Rank fields
to Number (Long).

Add a field to this new table for whether
you are appending Modeling, Strut, or Twirling.

Now make an append query based on each of the
3 xtabs where you supply appropriate string for
that new field value and all 3 xtabs end up being
in one table.

Does that make sense?

Appending results of xtab should cast Score/Rank
to Long, plus it is "thinner."

Once the values are in this new report table,
you should be able to do all kinds of SQL magic.

I'm sorry but Monday is crunch time here (plus
we have big ice storm coming), so have to be brief.

If you want, you can zip mdb and attach to email
to me (not sure how soon can look at it -- may not
have electricity by tonight for who knows how long).

g
a
r
y
l
w
<at>
w
a
m
e
g
o
<dot>
n
e
t

that produces 0's just as you expected but character (string) not numeric
0.

the record source for the report is actually a query with 3 crosstabs (as
you designed for me) and 2 other tables. In that query I can use NZ() to
get
numeric values where needed. One crosstab is for Modeling, one for Strut,
and one for Twirling. The overall score is the sum of 25% of Modeling,
35%
of strut and 40% of Twirling. I also have to calculate a ranking within
each
event based on the sum of scores for that event.

the final report is so wide it has to be printed landscape on legal paper
in
a very small font if there is more than 2 judges.

thanks for your help.
--
Steve S

:
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

 

[Steve S]

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.

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

 

[Gary Walter]

Hi Steve,

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

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.

:
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