iif in a report design

[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)?

 

[Duane Hookom]

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

 

[Kelvin]

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]

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

 

[Duane Hookom]

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

 

[John Wood]

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

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

.

 

[John Wood]

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

.

 

[John Wood]

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

weeks.

I

teachers.

The


.

.

 

[Duane Hookom]

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

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

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



.

.