By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,467 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Having Zeros Appear

P: n/a
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
******

Nov 13 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
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
******

Nov 13 '05 #2

P: n/a
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
******

Nov 13 '05 #3

P: n/a
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
*********

Nov 13 '05 #4

P: n/a
"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.
Nov 13 '05 #5

P: n/a
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
*********

Nov 13 '05 #6

P: n/a
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.

Nov 13 '05 #7

P: n/a
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
********

Nov 13 '05 #8

P: n/a
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
******

Nov 13 '05 #9

P: n/a
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

Nov 13 '05 #10

P: n/a
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

Nov 13 '05 #11

P: n/a
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
*********

Nov 13 '05 #12

P: n/a
First I'd try something easy like:

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

James A. Fortune

Nov 13 '05 #13

P: n/a
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
*********

Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.