Having Zeros Appear

[Marcus]

I am running a query that calculates student enrolment per course. The
problem is, if the enrolment is zero, the entire row will not be
displayed.

I tried a variety of solutions, neither of which work:

IIf(IsNull([STUDENTLIST].[ENROLMENT])," ", [STUDENTLIST].[ENROLMENT])

IIf(IsNull([STUDENTLIST].[ENROLMENT]),"0", [STUDENTLIST].[ENROLMENT])

IIf([STUDENTLIST].[ENROLMENT]="0"),"0", [STUDENTLIST].[ENROLMENT])

Wondering if anyone knows how to solve this. I'm using Access 2002
with linked tables.
Marcus
******

[Alan Webb]

Marcus,
Try Nz(([STUDENTLIST].[ENROLMENT],0). Nz is a rather useful function which
will substitute a value (any value, actually) for a null.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Marcus" <to*******@yahoo.ca> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I am running a query that calculates student enrolment per course. The
problem is, if the enrolment is zero, the entire row will not be
displayed.

I tried a variety of solutions, neither of which work:

IIf(IsNull([STUDENTLIST].[ENROLMENT])," ", [STUDENTLIST].[ENROLMENT])

IIf(IsNull([STUDENTLIST].[ENROLMENT]),"0", [STUDENTLIST].[ENROLMENT])

IIf([STUDENTLIST].[ENROLMENT]="0"),"0", [STUDENTLIST].[ENROLMENT])

Wondering if anyone knows how to solve this. I'm using Access 2002
with linked tables.
Marcus
******

[Johnson Cheung]

Please post your query. Without looking at your query, I guess you
should use "left join":

select CourseName, Count(*) As StudendCount from Course as c left join
StudentList s on c.CourseId = s.CourseId Group By CourseName
"Marcus" <to*******@yahoo.ca> wrote in message news:<11*********************@g14g2000cwa.googlegr oups.com>...

I am running a query that calculates student enrolment per course. The
problem is, if the enrolment is zero, the entire row will not be
displayed.

I tried a variety of solutions, neither of which work:

IIf(IsNull([STUDENTLIST].[ENROLMENT])," ", [STUDENTLIST].[ENROLMENT])

IIf(IsNull([STUDENTLIST].[ENROLMENT]),"0", [STUDENTLIST].[ENROLMENT])

IIf([STUDENTLIST].[ENROLMENT]="0"),"0", [STUDENTLIST].[ENROLMENT])

Wondering if anyone knows how to solve this. I'm using Access 2002
with linked tables.
Marcus
******

[Marcus]

The code is as follows:

SELECT Count(SRS_CSECTION_STUDENTLIST_ASSOC.ENROLLMENT_LI ST_ID) AS
Enrol, SRS_COURSE.CODE, SRS_SECTION.CODE
FROM (SRS_STUDENT_LIST_ITEM INNER JOIN
SRS_CSECTION_STUDENTLIST_ASSOC
ON SRS_STUDENT_LIST_ITEM.STUDENT_LIST_ID =
SRS_CSECTION_STUDENTLIST_ASSOC.ENROLLMENT_LIST_ID) INNER JOIN
(SRS_SECTION INNER JOIN SRS_COURSE ON SRS_SECTION.COURSE_ID =
SRS_COURSE.COURSE_ID) ON
SRS_CSECTION_STUDENTLIST_ASSOC.COURSE_SECTION_ID =
SRS_SECTION.SECTION_ID
GROUP BY SRS_COURSE.CODE, SRS_SECTION.CODE;

Marcus
*********

[Bob Quintal]

"Marcus" <to*******@yahoo.ca> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

The code is as follows:

SELECT
Count(SRS_CSECTION_STUDENTLIST_ASSOC.ENROLLMENT_LI ST_ID) AS
Enrol, SRS_COURSE.CODE, SRS_SECTION.CODE
FROM (SRS_STUDENT_LIST_ITEM INNER JOIN
SRS_CSECTION_STUDENTLIST_ASSOC
ON SRS_STUDENT_LIST_ITEM.STUDENT_LIST_ID =
SRS_CSECTION_STUDENTLIST_ASSOC.ENROLLMENT_LIST_ID) INNER JOIN
(SRS_SECTION INNER JOIN SRS_COURSE ON SRS_SECTION.COURSE_ID =
SRS_COURSE.COURSE_ID) ON
SRS_CSECTION_STUDENTLIST_ASSOC.COURSE_SECTION_ID =
SRS_SECTION.SECTION_ID
GROUP BY SRS_COURSE.CODE, SRS_SECTION.CODE;

Marcus
*********

SELECT
Count(SRS_CSECTION_STUDENTLIST_ASSOC.ENROLLMENT_LI ST_ID)
AS Enrol,
SRS_COURSE.CODE,
SRS_SECTION.CODE
FROM (SRS_STUDENT_LIST_ITEM
right JOIN
SRS_CSECTION_STUDENTLIST_ASSOC
ON SRS_STUDENT_LIST_ITEM.STUDENT_LIST_ID =
SRS_CSECTION_STUDENTLIST_ASSOC.ENROLLMENT_LIST_ID)
right JOIN
(SRS_SECTION
INNER JOIN
SRS_COURSE
ON SRS_SECTION.COURSE_ID = SRS_COURSE.COURSE_ID)
ON SRS_CSECTION_STUDENTLIST_ASSOC.COURSE_SECTION_ID =
SRS_SECTION.SECTION_ID
GROUP BY
SRS_COURSE.CODE,
SRS_SECTION.CODE;

the right joins should fix it. You may also need to change the
remaining inner join to a left join, as some versions of Access
may mis-interpret.

From the query design mode, double-click the join lines to change
the join type from the popup menu. Right joins are type 3.

--
Bob Quintal

PA is y I've altered my email address.

[Marcus]

I created a right join and the query worked. Many thanks.

However, I have noticed a small issue. It seems that the following
column

Count(SRS_CSECTION_STUDENTLIST*_ASSOC.ENROLLMENT_L IST_ID)AS Enrol

is counting a zero enrolment as 1. I found this odd. I assume this
means that only rows are being counted, and not the actualEssentially
the "Enrol" column provides the correct count, but if there are no
students in a class the column prints "1" instead of "0".

There is no way of me being able to distinguish between a class
enrolment of "1" with a class enrolment of "0".

Is there a way I can stop thsi from happening?

I have tried

Enrol: Count(Nz(SRS_CSECTION_STUDENTLIST_ASSOC.ENROLLMENT _LIST_ID,0))

but this doesn't solve the problem.
Marcus
*********

[Marcus]

I created a right join and the query worked. Many thanks.

However, I have noticed a small issue. It seems that the following
column

Count(SRS_CSECTION_STUDENTLIST*_ASSOC.ENROLLMENT_L IST_ID)AS Enrol

is counting a zero enrolment as 1. I found this odd. I assume this
means that only rows are being counted, and not the actualEssentially
the "Enrol" column provides the correct count, but if there are no
students in a class the column prints "1" instead of "0".

There is no way of me being able to distinguish between a class
enrolment of "1" with a class enrolment of "0".

Is there a way I can stop this from happening?

I have tried

Enrol: Count(Nz(SRS_CSECTION_STUDENTLIST_ASSOC.ENROLLMENT _LIST_ID,0))

but this doesn't solve the problem.

[Marcus]

I created a right join and the query worked. Many thanks.

However, I have noticed a small issue. It seems that the following
column

Count(SRS_CSECTION_STUDENTLIST**_ASSOC.ENROLLMENT_ LIST_ID)AS Enrol

is counting a zero enrolment as 1. I found this odd. I assume this
means that only rows are being counted, and not the actual enrolment
field. Essentially the "Enrol" column provides the correct count, but
if there are no
students in a class the column prints "1" instead of "0".

There is no way of me being able to distinguish between a class
enrolment of "1" with a class enrolment of "0".

Is there a way I can stop this from happening?

I have tried

Enrol:
Count(Nz(SRS_CSECTION_STUDENTL*IST_ASSOC.ENROLLMEN T_LIST_ID,0*))

but this doesn't solve the problem.

Marcus
********

[Marcus]

I created a right join and the query worked. Many thanks.

However, I have noticed a small issue. It seems that the following
column

Count(SRS_CSECTION_STUDENTLIST*_ASSOC.ENROLLMENT_L IST_ID)AS Enrol

is counting a zero enrolment as 1. I found this odd. I assume this
means that only rows are being counted, and not the actual enrolment
field. Essentially the "Enrol" column provides the correct count, but
if there are no
students in a class the column prints "1" instead of "0".

There is no way of me being able to distinguish between a class
enrolment of "1" or a class enrolment of "0".

Is there a way I can stop this from happening?

I have tried

Enrol: Count(Nz(SRS_CSECTION_STUDENTLIST_ASSOC.ENROLLMENT _LIST_ID,0))

but this doesn't solve the problem.
Marcus
******

[jimfortune]

To avoid confusion it is helpful to know that a right join is
equivalent to a left join with the tables/sources reversed. E.g.,
table1 with a type 3 right join to table2 is equivalent to table2 with
a type 2 left join to table1. So Bob's query has an equivalent query
that uses left joins instead of right joins. There's nothing
mysterious about the right joins.

James A. Fortune

[jimfortune]

You are correct about the rows being counted. If ENROLLMENT_LIST_ID is
always a 1 or a 0 you can use Sum() instead of Count(). Include Nz()
if null values are possible. Use Val(Nz()) if the 1's and 0's are text
and null values are possible. If ENROLLMENT_LIST_ID can be greater
than one, forcing you to use Count() then do something like
Sum(Abs(Nz(ENROLLMENT_LIST_ID) > 0)) AS Enrol or
Sum(Abs(Val(Nz(ENROLLMENT_LIST_ID)) > 0)) AS Enrol if text.

James A. Fortune

[Marcus]

ENROLLMENT_LIST_ID is not always 1 or 0. ENROLLMENT_LIST_ID is a
number that is assigned to a student when they register for a course.
So, ENROLLMENT_LIST_ID could be 914131, or 56787 etc.

Essentially, I use Count(ENROLLMENT_LIST_ID) to determine how many
students are in a course. Really what I need to do is

IIf(Count(ENROLLMENT_LIST_ID) = 1, 1, 0)

but this is not accepted in MS Access, as I get an error when I run it.
The error says that "Count" is not allowed in this circumstance.

To determine whether a Count(ENROLLMENT_LIST_ID) is "1" (one student
registered in the course) or "0" (no students registered in the
course), I should really be determining whether any studentIDs are
associated with Count(ENROLLMENT_LIST_ID). For instance, I could say

IIf([PersonID]="",0,Count(ENROLLMENT_LIST_ID))

I would think that in this case, "Count" is not accepted by MS Access
either.

Is there another way to do this?
Marcus
*********

[jimfortune]

First I'd try something easy like:

SELECT Count(ENROLLMENT_LIST_ID) AS ECount, IIf([ECount] = 1, 1, 0) AS
Enrol, ...

James A. Fortune

[MacDermott]

You could use something like this:
SUM(IIF(ENROLLMENT_LIST_ID)=0,0,1)

Or, it might be appropriate to add a criteria line to your whole query:
HAVING ENROLLMENT_LIST_ID>0
"Marcus" <to*******@yahoo.ca> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

ENROLLMENT_LIST_ID is not always 1 or 0. ENROLLMENT_LIST_ID is a
number that is assigned to a student when they register for a course.
So, ENROLLMENT_LIST_ID could be 914131, or 56787 etc.

Essentially, I use Count(ENROLLMENT_LIST_ID) to determine how many
students are in a course. Really what I need to do is

IIf(Count(ENROLLMENT_LIST_ID) = 1, 1, 0)

but this is not accepted in MS Access, as I get an error when I run it.
The error says that "Count" is not allowed in this circumstance.

To determine whether a Count(ENROLLMENT_LIST_ID) is "1" (one student
registered in the course) or "0" (no students registered in the
course), I should really be determining whether any studentIDs are
associated with Count(ENROLLMENT_LIST_ID). For instance, I could say

IIf([PersonID]="",0,Count(ENROLLMENT_LIST_ID))

I would think that in this case, "Count" is not accepted by MS Access
either.

Is there another way to do this?
Marcus
*********