Enrollment Database Query help Needed - Low Enrolled classes only

[garydave2]

I am the database person for an art school with an Access 2K database
with many linked tables. I need to constuct a query to produce a list
of all the past classes with an enrollment of less than 2 students.
How do I construct this query? I have the linked tables a
tblRegistrations, tblClasses, tblClasstitle, and tblClassinstructor.
I want the final query to have ClassTitle, ClassInstructor, and
ClassEnrollment Total (based on tblRegistrations which has one record
per student registration), filtered to only include classes with <3
students.

This probably requires two queries, one of which references the
other. Can anyone give me some guidance on the SQL to construct the
required queries?

Thanks!!

Gary

 

[Guest]

Hi,

Post the SQL that will show the ClassTitle, ClassInstructor, something like
the class number, and the students that took the class. Then we can help you
create the Totals query.

You have to be carefull on the join. For example I have taught multiple
Access classes with the same ClassTitle. If you don't join the particular
class, then it could look like I had many, many students in one ClassTitle;
however, I actually had few students in many classes.

 

[tina]

well, you don't say how any of these tables are related. so i'll guess that
1) tblRegistrations has one record for each student enrolled in each class.
2) tblClasses has a one-to-many link to tblRegistrations.
3) tblClasstitle has a one-to-many link to tblClasses.
4) tblClassinstructor has a one-to-many link to tblClasses.

based on the above, suggest you build the query with tblRegistrations having
a LEFT JOIN to tblClasses, which in turn has a LEFT JOIN to tblClasstitle
and a LEFT JOIN to tblClassinstructor. pull the foreign key field from
tblRegistrations that links the table to tblClasses, the ClassTitle and
ClassInstructor from their respective tables, and the primary key field from
tblRegistrations, into the query grid. change the query to a Totals query
(View | Totals on the menu bar). all fields in the grid will default to
GroupBy; change the primary key field to Count. set criteria on that field
to <2.

hth

 

[Michael Gramelspacher]

I am the database person for an art school with an Access 2K database
with many linked tables. I need to constuct a query to produce a list
of all the past classes with an enrollment of less than 2 students.
How do I construct this query? I have the linked tables a
tblRegistrations, tblClasses, tblClasstitle, and tblClassinstructor.
I want the final query to have ClassTitle, ClassInstructor, and
ClassEnrollment Total (based on tblRegistrations which has one record
per student registration), filtered to only include classes with <3
students.

This probably requires two queries, one of which references the
other. Can anyone give me some guidance on the SQL to construct the
required queries?

Thanks!!

Gary

Well, I have a table ClassSchedules and a table
StudentClassSchedules and I think I would do it so:

SELECT c.class_id,
c.section_nbr,
(SELECT COUNT(* )
FROM StudentClassSchedules AS s
WHERE s.class_id = c.class_id
AND s.section_nbr = c.section_nbr
AND s.semester_year = c.semester_year
AND s.semester_nbr = c.semester_nbr) AS
Enrolment
FROM ClassSchedules AS c
WHERE (c.semester_year <> 2007
AND c.semester_nbr <> 1)
AND 2 < (SELECT COUNT(* )
FROM StudentClassSchedules AS s
WHERE s.class_id = c.class_id
AND s.section_nbr = c.section_nbr
AND s.semester_year = c.semester_year
AND s.semester_nbr = c.semester_nbr);

 

[Michael Gramelspacher]

correction:

SELECT c.class_id, c.section_nbr, (SELECT COUNT(* )
FROM StudentClassSchedules AS s
WHERE s.class_id = c.class_id
AND s.section_nbr = c.section_nbr
AND s.semester_year = c.semester_year
AND s.semester_nbr = c.semester_nbr) AS
Enrollment
FROM ClassSchedules AS c
WHERE (c.semester_year <> 2007
AND c.semester_nbr <> 1)
AND 2 > (SELECT COUNT(* )
FROM StudentClassSchedules AS s
WHERE s.class_id = c.class_id
AND s.section_nbr = c.section_nbr
AND s.semester_year = c.semester_year
AND s.semester_nbr = c.semester_nbr);

 

[garydave2]

Everyone -

Thanks for the help! Tina's suggestion about using the PK to do the
count was particularly helpful. Everyone's assumptions about the
table joins where absolutely correct. Here's the SQL that did the
job:

SELECT Count(tblClassReg.ClassID) AS CountOfClassID,
tblClasstitle.ClassName, tblInstructors.InstructorName,
tblclasslevel.CLASS_LEVE, tblClasses.StartDate
FROM tblInstructors RIGHT JOIN (tblClasstitle RIGHT JOIN
(tblclasslevel RIGHT JOIN (tblClasses RIGHT JOIN tblClassReg ON
tblClasses.ClassID = tblClassReg.ClassID) ON
tblclasslevel.ClasslevelID = tblClasses.ClassLevelID) ON
tblClasstitle.ClassNameID = tblClasses.ClasstitleID) ON
tblInstructors.InstructorID = tblClasses.InstructorID
GROUP BY tblClasstitle.ClassName, tblInstructors.InstructorName,
tblclasslevel.CLASS_LEVE, tblClasses.StartDate
HAVING (((Count(tblClassReg.ClassID))<2) AND
((tblClasses.StartDate)>#1/1/2001#));

Gary