iif in a report design

  • Thread starter Thread starter John Wood
  • Start date Start date
J

John Wood

I am making a report and have unioned several crosstab
queries. One of the querries does not have any entries for
a class, so I dummied a second copy of another class
(math). When I am typing the report I tried typing:
=iif([grclass]="read3"," ",[life skills/art])
The Read3 class had no people in the Life Skills/Art
class. I am getting blank entries for the read3 life
skills (like I want), but for the other classes the Life
Skills is showing #Error.
But for the total part =sum([life skills/art]) is giving
me the correct class total.

Note: the crosstab querries are created from a single
select query - all the grades for a single 6 weeks. I am
working at a small school; 100 students 6 teachers. The
crosstabs are creating the number of each grade for each
class. The students as a class stay together all day.

Your help would be greatly appreciated.

What would happen if I did not know that a class does not
have any entries? Or if I ran my crosstabs the other
direction - new would be letter grades on top and new with
classes on the side. How would I know/deal with a class
not having all the different letter grades (Some classes
have A- and some do not, OR some class does poorly and has
no A's or better yet no F's)?
 
It would help to know where you are starting and where you want to go:
Where you are starting:
-table/field structure
-sample data
Where you want to go
-brief description
-example display that corresponds with your sample data
 
In your IIF statement you are setting the true condition to a space, " ".
Since [life skills/art] is a number you need to use a number for the true
condition.

=Iif([grclass]="read3",0,[life skills/art])

Kelvin
 
I am working at a small school - 100 students and 6
teachers. I have a table that has 'all' the grades. I
have made a query that has all of the grades for this 6
weeks. I originally made a crosstab query to show how
many of each grade each teacher/subject 'gave'. When I
started to print the GPA, I realized that I would want the
numbers for each individual class, so I created 6 crosstab
querries - 1 for the AOT class, 1 for the read3 class,
etc. This is appropriate since every AOT student has the
same schedule. My problem occurred since the Read3 has
Math for 2 periods, even though they only get 1 credit,
and consequently miss their Life Skills/Art teacher.

My report should look like this:
AOT class
LetGrade History English Health Life Skills/Art Math
P.E. Science
A 5 3 2 4 2
3 5
A- 2 1 3
3 1 4

I hope this provides the information that helps determine
the problem and solution. I will be very glad to answer
other questions.
I see some people talk about subreports. I do not know
how to create a subreport, although I have created forms
with a subform. (I used it to enter the grades.)
Thank you.
-----Original Message-----
It would help to know where you are starting and where you want to go:
Where you are starting:
-table/field structure
-sample data
Where you want to go
-brief description
-example display that corresponds with your sample data

--
Duane Hookom
MS Access MVP


John Wood said:
I am making a report and have unioned several crosstab
queries. One of the querries does not have any entries for
a class, so I dummied a second copy of another class
(math). When I am typing the report I tried typing:
=iif([grclass]="read3"," ",[life skills/art])
The Read3 class had no people in the Life Skills/Art
class. I am getting blank entries for the read3 life
skills (like I want), but for the other classes the Life
Skills is showing #Error.
But for the total part =sum([life skills/art]) is giving
me the correct class total.

Note: the crosstab querries are created from a single
select query - all the grades for a single 6 weeks. I am
working at a small school; 100 students 6 teachers. The
crosstabs are creating the number of each grade for each
class. The students as a class stay together all day.

Your help would be greatly appreciated.

What would happen if I did not know that a class does not
have any entries? Or if I ran my crosstabs the other
direction - new would be letter grades on top and new with
classes on the side. How would I know/deal with a class
not having all the different letter grades (Some classes
have A- and some do not, OR some class does poorly and has
no A's or better yet no F's)?
Click to expand...
Click to expand...
 
Again:
Where you are starting:
-table/field structure
-sample data


--
Duane Hookom
MS Access MVP


John Wood said:
I am working at a small school - 100 students and 6
teachers. I have a table that has 'all' the grades. I
have made a query that has all of the grades for this 6
weeks. I originally made a crosstab query to show how
many of each grade each teacher/subject 'gave'. When I
started to print the GPA, I realized that I would want the
numbers for each individual class, so I created 6 crosstab
querries - 1 for the AOT class, 1 for the read3 class,
etc. This is appropriate since every AOT student has the
same schedule. My problem occurred since the Read3 has
Math for 2 periods, even though they only get 1 credit,
and consequently miss their Life Skills/Art teacher.

My report should look like this:
AOT class
LetGrade History English Health Life Skills/Art Math
P.E. Science
A 5 3 2 4 2
3 5
A- 2 1 3
3 1 4

I hope this provides the information that helps determine
the problem and solution. I will be very glad to answer
other questions.
I see some people talk about subreports. I do not know
how to create a subreport, although I have created forms
with a subform. (I used it to enter the grades.)
Thank you.
-----Original Message-----
It would help to know where you are starting and where you want to go:
Where you are starting:
-table/field structure
-sample data
Where you want to go
-brief description
-example display that corresponds with your sample data

--
Duane Hookom
MS Access MVP


John Wood said:
I am making a report and have unioned several crosstab
queries. One of the querries does not have any entries for
a class, so I dummied a second copy of another class
(math). When I am typing the report I tried typing:
=iif([grclass]="read3"," ",[life skills/art])
The Read3 class had no people in the Life Skills/Art
class. I am getting blank entries for the read3 life
skills (like I want), but for the other classes the Life
Skills is showing #Error.
But for the total part =sum([life skills/art]) is giving
me the correct class total.

Note: the crosstab querries are created from a single
select query - all the grades for a single 6 weeks. I am
working at a small school; 100 students 6 teachers. The
crosstabs are creating the number of each grade for each
class. The students as a class stay together all day.

Your help would be greatly appreciated.

What would happen if I did not know that a class does not
have any entries? Or if I ran my crosstabs the other
direction - new would be letter grades on top and new with
classes on the side. How would I know/deal with a class
not having all the different letter grades (Some classes
have A- and some do not, OR some class does poorly and has
no A's or better yet no F's)?
Click to expand...
Click to expand...
Click to expand...
 
Nice idea, BUT my report is showing blanks for the 0's
(which is fine with me, and that would be another
interesting question). Changing as you indicated had no
results - the true section still showed blanks, and false
is showing #Error.
-----Original Message-----
In your IIF statement you are setting the true condition to a space, " ".
Since [life skills/art] is a number you need to use a number for the true
condition.

=Iif([grclass]="read3",0,[life skills/art])

Kelvin

John Wood said:
I am making a report and have unioned several crosstab
queries. One of the querries does not have any entries for
a class, so I dummied a second copy of another class
(math). When I am typing the report I tried typing:
=iif([grclass]="read3"," ",[life skills/art])
The Read3 class had no people in the Life Skills/Art
class. I am getting blank entries for the read3 life
skills (like I want), but for the other classes the Life
Skills is showing #Error.
But for the total part =sum([life skills/art]) is giving
me the correct class total.

Note: the crosstab querries are created from a single
select query - all the grades for a single 6 weeks. I am
working at a small school; 100 students 6 teachers. The
crosstabs are creating the number of each grade for each
class. The students as a class stay together all day.

Your help would be greatly appreciated.

What would happen if I did not know that a class does not
have any entries? Or if I ran my crosstabs the other
direction - new would be letter grades on top and new with
classes on the side. How would I know/deal with a class
not having all the different letter grades (Some classes
have A- and some do not, OR some class does poorly and has
no A's or better yet no F's)?
Click to expand...


.
Click to expand...
 
I am starting with 2 (3) tables [1] students names, [2]
Grades and [3] Grade Point Average
Sample [1]
ID Key KA# Last Name First Name
23 3428 Smith Joe
86 4739 Jones Fred

Sample [2]
GrID Key Stud# Year Semester Period GrClass
Subject LetGrade
37 23 2004 spring 1st 6 wk AOT Math
B+
43 23 2004 spring 1st 6 wk AOT
English C
44 23 2004 spring 1st 6 wk AOT
Science A-
54 86 2004 spring 1st 6 wk Read3
Math D
55 86 2004 spring 1st 6 wk Read3
English F
56 86 2004 spring 1st 6 wk Read3
Science C-
-----Original Message-----
Again:
Where you are starting:
-table/field structure
-sample data


--
Duane Hookom
MS Access MVP


John Wood said:
I am working at a small school - 100 students and 6
teachers. I have a table that has 'all' the grades. I
have made a query that has all of the grades for this 6
weeks. I originally made a crosstab query to show how
many of each grade each teacher/subject 'gave'. When I
started to print the GPA, I realized that I would want the
numbers for each individual class, so I created 6 crosstab
querries - 1 for the AOT class, 1 for the read3 class,
etc. This is appropriate since every AOT student has the
same schedule. My problem occurred since the Read3 has
Math for 2 periods, even though they only get 1 credit,
and consequently miss their Life Skills/Art teacher.

My report should look like this:
AOT class
LetGrade History English Health Life Skills/Art Math
P.E. Science
A 5 3 2 4 2
3 5
A- 2 1 3
3 1 4

I hope this provides the information that helps determine
the problem and solution. I will be very glad to answer
other questions.
I see some people talk about subreports. I do not know
how to create a subreport, although I have created forms
with a subform. (I used it to enter the grades.)
Thank you.
-----Original Message-----
It would help to know where you are starting and where you want to go:
Where you are starting:
-table/field structure
-sample data
Where you want to go
-brief description
-example display that corresponds with your sample data

--
Duane Hookom
MS Access MVP


I am making a report and have unioned several crosstab
queries. One of the querries does not have any
Click to expand...
entries
for
a class, so I dummied a second copy of another class
(math). When I am typing the report I tried typing:
=iif([grclass]="read3"," ",[life skills/art])
The Read3 class had no people in the Life Skills/Art
class. I am getting blank entries for the read3 life
skills (like I want), but for the other classes the Life
Skills is showing #Error.
But for the total part =sum([life skills/art]) is giving
me the correct class total.

Note: the crosstab querries are created from a single
select query - all the grades for a single 6 weeks.
Click to expand...
I
am
working at a small school; 100 students 6 teachers. The
crosstabs are creating the number of each grade for each
class. The students as a class stay together all day.

Your help would be greatly appreciated.

What would happen if I did not know that a class does not
have any entries? Or if I ran my crosstabs the other
direction - new would be letter grades on top and new with
classes on the side. How would I know/deal with a class
not having all the different letter grades (Some classes
have A- and some do not, OR some class does poorly
Click to expand...
and
has
no A's or better yet no F's)?
Click to expand...
Click to expand...


.
Click to expand...
 
I seemed to solved/avoided the problem by using a single
crosstab query for all the grades with appropriate
criteria, and then grouping the report. In order to do
what I wanted on the crosstab query I put up a couple of
row headers (without Letgrade), and then the column
header, and then put the value as LetGrade (count), and
then I went back to the row headers and added LetGrade.
For the report I tried to include the total for each
letter grade within a class, and if not every class had at
least one, it gave the total as blank. Is there a way of
changing this if the total was not included in the
crosstab query?
-----Original Message-----
I am starting with 2 (3) tables [1] students names, [2]
Grades and [3] Grade Point Average
Sample [1]
ID Key KA# Last Name First Name
23 3428 Smith Joe
86 4739 Jones Fred

Sample [2]
GrID Key Stud# Year Semester Period GrClass
Subject LetGrade
37 23 2004 spring 1st 6 wk AOT Math
B+
43 23 2004 spring 1st 6 wk AOT
English C
44 23 2004 spring 1st 6 wk AOT
Science A-
54 86 2004 spring 1st 6 wk Read3
Math D
55 86 2004 spring 1st 6 wk Read3
English F
56 86 2004 spring 1st 6 wk Read3
Science C-
-----Original Message-----
Again:
Where you are starting:
-table/field structure
-sample data


--
Duane Hookom
MS Access MVP


John Wood said:
I am working at a small school - 100 students and 6
teachers. I have a table that has 'all' the grades. I
have made a query that has all of the grades for this 6
weeks. I originally made a crosstab query to show how
many of each grade each teacher/subject 'gave'. When I
started to print the GPA, I realized that I would want the
numbers for each individual class, so I created 6 crosstab
querries - 1 for the AOT class, 1 for the read3 class,
etc. This is appropriate since every AOT student has the
same schedule. My problem occurred since the Read3 has
Math for 2 periods, even though they only get 1 credit,
and consequently miss their Life Skills/Art teacher.

My report should look like this:
AOT class
LetGrade History English Health Life Skills/Art Math
P.E. Science
A 5 3 2 4 2
3 5
A- 2 1 3
3 1 4

I hope this provides the information that helps determine
the problem and solution. I will be very glad to answer
other questions.
I see some people talk about subreports. I do not know
how to create a subreport, although I have created forms
with a subform. (I used it to enter the grades.)
Thank you.
-----Original Message-----
It would help to know where you are starting and where
you want to go:
Where you are starting:
-table/field structure
-sample data
Where you want to go
-brief description
-example display that corresponds with your sample data

--
Duane Hookom
MS Access MVP


I am making a report and have unioned several crosstab
queries. One of the querries does not have any entries
for
a class, so I dummied a second copy of another class
(math). When I am typing the report I tried typing:
=iif([grclass]="read3"," ",[life skills/art])
The Read3 class had no people in the Life Skills/Art
class. I am getting blank entries for the read3 life
skills (like I want), but for the other classes the Life
Skills is showing #Error.
But for the total part =sum([life skills/art]) is giving
me the correct class total.

Note: the crosstab querries are created from a single
select query - all the grades for a single 6
Click to expand...
Click to expand...
weeks.
I
Click to expand...
teachers.
The


.
Click to expand...
.
Click to expand...
 
This transform will return 0 rather than Null values.
TRANSFORM Nz(Count([tblGrades].[GrID]),0) AS CountOfGrID
SELECT tblGrades.GrClass, tblGrades.LetGrade
FROM tblStudents INNER JOIN tblGrades ON tblStudents.ID = tblGrades.StudID
GROUP BY tblGrades.GrClass, tblGrades.LetGrade
PIVOT tblGrades.Subject;

--
Duane Hookom
MS Access MVP


John Wood said:
I seemed to solved/avoided the problem by using a single
crosstab query for all the grades with appropriate
criteria, and then grouping the report. In order to do
what I wanted on the crosstab query I put up a couple of
row headers (without Letgrade), and then the column
header, and then put the value as LetGrade (count), and
then I went back to the row headers and added LetGrade.
For the report I tried to include the total for each
letter grade within a class, and if not every class had at
least one, it gave the total as blank. Is there a way of
changing this if the total was not included in the
crosstab query?
-----Original Message-----
I am starting with 2 (3) tables [1] students names, [2]
Grades and [3] Grade Point Average
Sample [1]
ID Key KA# Last Name First Name
23 3428 Smith Joe
86 4739 Jones Fred

Sample [2]
GrID Key Stud# Year Semester Period GrClass
Subject LetGrade
37 23 2004 spring 1st 6 wk AOT Math
B+
43 23 2004 spring 1st 6 wk AOT
English C
44 23 2004 spring 1st 6 wk AOT
Science A-
54 86 2004 spring 1st 6 wk Read3
Math D
55 86 2004 spring 1st 6 wk Read3
English F
56 86 2004 spring 1st 6 wk Read3
Science C-
-----Original Message-----
Again:
Where you are starting:
-table/field structure
-sample data


--
Duane Hookom
MS Access MVP


I am working at a small school - 100 students and 6
teachers. I have a table that has 'all' the grades. I
have made a query that has all of the grades for this 6
weeks. I originally made a crosstab query to show how
many of each grade each teacher/subject 'gave'. When I
started to print the GPA, I realized that I would want the
numbers for each individual class, so I created 6 crosstab
querries - 1 for the AOT class, 1 for the read3 class,
etc. This is appropriate since every AOT student has the
same schedule. My problem occurred since the Read3 has
Math for 2 periods, even though they only get 1 credit,
and consequently miss their Life Skills/Art teacher.

My report should look like this:
AOT class
LetGrade History English Health Life Skills/Art Math
P.E. Science
A 5 3 2 4 2
3 5
A- 2 1 3
3 1 4

I hope this provides the information that helps determine
the problem and solution. I will be very glad to answer
other questions.
I see some people talk about subreports. I do not know
how to create a subreport, although I have created forms
with a subform. (I used it to enter the grades.)
Thank you.
-----Original Message-----
It would help to know where you are starting and where
you want to go:
Where you are starting:
-table/field structure
-sample data
Where you want to go
-brief description
-example display that corresponds with your sample data

--
Duane Hookom
MS Access MVP


I am making a report and have unioned several crosstab
queries. One of the querries does not have any entries
for
a class, so I dummied a second copy of another class
(math). When I am typing the report I tried typing:
=iif([grclass]="read3"," ",[life skills/art])
The Read3 class had no people in the Life Skills/Art
class. I am getting blank entries for the read3 life
skills (like I want), but for the other classes the Life
Skills is showing #Error.
But for the total part =sum([life skills/art]) is giving
me the correct class total.

Note: the crosstab querries are created from a single
select query - all the grades for a single 6
Click to expand...
weeks.
I
am
working at a small school; 100 students 6
Click to expand...
teachers.
The
crosstabs are creating the number of each grade for each
class. The students as a class stay together all day.

Your help would be greatly appreciated.

What would happen if I did not know that a class does
not
have any entries? Or if I ran my crosstabs the other
direction - new would be letter grades on top and new
with
classes on the side. How would I know/deal with a class
not having all the different letter grades (Some classes
have A- and some do not, OR some class does poorly and
has
no A's or better yet no F's)?



.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top