Renaming Column Headers with IIf as a Result of a Crosstab Query

[Ricco]

Greetings all,

I've done a search on the topic and could not find a solution to this
particular problem:

I've created a crosstab query that has successfully generated the correct
results I need; however, the column headers need to be changed and I can't
seem to write the correct expression to accomplish this. By the way, this is
for a Case Exercise right out of a beginner's tutorial book so it does not
use some of the more advanced methods this could probably be solved with.
I'll write down the exact instructions from the text.

"Create a crosstab query based on the tblContract table. use the LessonType
field values for the row headings, the LessonLength field values for the
column headings, and the count of the ContractID field values as the
summarized value, and include row sums. Save the query as
qryLessonTypeCrosstab." I'm golden up to this point.

The instructions go on to say, "Change the column heading for the row sum
column to Total Number of Lessons," I've even gotten this far successfully,
"and change the column heading for the [LessonLength] columns to Number of
30-Minute Lessons and Number of 60-Minute Lessons.

What I've tried, but to no avail are the following:

LessonLength: IIf([LessonLength]="30", "Number of 30-Minute Lessons",
"Number of 60-Minute Lessons")

IIf([LessonLength]="30", "Number of 30-Minute Lessons", "Number of 60-Minute
Lessons")

The examples I've seen in this case have been that of a yes/no or true/false
scenario. In this case, only two values are present - 30 or 60 (minutes), and
for the life of me I just can't figure out the correct expression to get it
to look at if it's "30" to generate a true statement, or if it's not "30", so
that it may generate a false statement, and wind up under their corresponding
columns accordingly.

By the way, the LessonType field lists different instruments (guitar,
violin, etc.), ContractID field is a primary key field (which lists contract
numbers), and LessonLength has a value of only 30 or 60. How far (or close)
am I from what I need to do, any tips? Thanks in advice for your time and
information.

 

[John Spencer MVP]

First if the lesson length is a number field then your IIF would be

LessonLength: IIF([TableName].[LessonLength]=30,"Number of 30-Minute
Lessons","Number of 60-Minute Lessons")

Alternative would be:

LessonLength: "Number of " & [TableName].[LessonLength] & "-Minute Lessons"

Note that I included the tableName along with the field name. This is to
preclude a circular reference error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ricco]

Hello, and thanks for replying. Your proposed solution appears like two
separate expressions I'd have to enter somewhere. How would I accomplish this
if I only have one place where I can write it to: [LessonLength] field?

I was able to successfully rename the ContractID row sum heading by simply
renaming the field name to:Total Number of Lessons: ContractID, and it
generates the results I need, so I don't believe it is necessary to include
that field into any of these expressions (as you've indicated in your second
set of proposed solutions). Additionally, by default, the ContractID is a
primary key field so it will never yield null values.

Please review the following screen captures to better illustrate where I'm at:

http://i6.photobucket.com/albums/y216/CENSO/CrosstabQuery.jpg
(note that the expression I've entered yields an error message and does not
successfully run the query)

And this screen capture illustrates where I need to be:
http://i6.photobucket.com/albums/y216/CENSO/CrosstabQuerySolution.jpg

Ooops, not what you want. I'll try again.
Number of 30Minute Lessons: Sum(Iif(LessonLength=30,LessonLength,0))/30
Number of 60Minute Lessons: Sum(Iif(LessonLength=60,LessonLength,0))/60
or possibly
Number of 30Minute Lessons: Count(Iif(LessonLength=30,ContractID,Null))
Number of 60Minute Lessons: Count(Iif(LessonLength=60,ContractID,Null))

Believe your two columns would be like:
Number of 30Minute Lessons: Sum(Iif(LessonLength=30,LessonLength,0))
Number of 60Minute Lessons: Sum(Iif(LessonLength=60,LessonLength,0))

Greetings all,

[quoted text clipped - 39 lines]

am I from what I need to do, any tips? Thanks in advice for your time and
information.

 

[Ricco]

Thank you for replying, John,

Basically, I was on the right path with my methodology in constructing the
expression and the only difference between your solution and the one I
attempted is the inclusion of the [TableName] (I would have never thought of
that) and the exclusion of the parentheses around the 30.

Needless to say, your first suggestion did the trick. Now, instead of me
running along with the correct answer, I'd like to know 'why' it worked. With
regards to the circular reference error (which I at one point did encounter),
first of all, why do you even need to indicate a table when the fields used
for the query derived originally from the table in question, and secondly,
why is it that you cannot select the table in the drop down menu right
underneath the field name in the query design view and have it work that way?
(it doesn't, but I'm just curious)

Ricco

First if the lesson length is a number field then your IIF would be

LessonLength: IIF([TableName].[LessonLength]=30,"Number of 30-Minute
Lessons","Number of 60-Minute Lessons")

Alternative would be:

LessonLength: "Number of " & [TableName].[LessonLength] & "-Minute Lessons"

Note that I included the tableName along with the field name. This is to
preclude a circular reference error.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Greetings all,

I've done a search on the topic and could not find a solution to this
particular problem:

I've created a crosstab query that has successfully generated the correct
results I need; however, the column headers need to be changed and I can't
seem to write the correct expression to accomplish this. By the way, this is
for a Case Exercise right out of a beginner's tutorial book so it does not
use some of the more advanced methods this could probably be solved with.
I'll write down the exact instructions from the text.

"Create a crosstab query based on the tblContract table. use the LessonType
field values for the row headings, the LessonLength field values for the
column headings, and the count of the ContractID field values as the
summarized value, and include row sums. Save the query as
qryLessonTypeCrosstab." I'm golden up to this point.

The instructions go on to say, "Change the column heading for the row sum
column to Total Number of Lessons," I've even gotten this far successfully,
"and change the column heading for the [LessonLength] columns to Number of
30-Minute Lessons and Number of 60-Minute Lessons.

What I've tried, but to no avail are the following:

LessonLength: IIf([LessonLength]="30", "Number of 30-Minute Lessons",
"Number of 60-Minute Lessons")

IIf([LessonLength]="30", "Number of 30-Minute Lessons", "Number of 60-Minute
Lessons")

The examples I've seen in this case have been that of a yes/no or true/false
scenario. In this case, only two values are present - 30 or 60 (minutes), and
for the life of me I just can't figure out the correct expression to get it
to look at if it's "30" to generate a true statement, or if it's not "30", so
that it may generate a false statement, and wind up under their corresponding
columns accordingly.

By the way, the LessonType field lists different instruments (guitar,
violin, etc.), ContractID field is a primary key field (which lists contract
numbers), and LessonLength has a value of only 30 or 60. How far (or close)
am I from what I need to do, any tips? Thanks in advice for your time and
information.