I need a better filter

[Parker]

I have a continuous form that keeps track of how many students are in a
class and how many open slots are left for each class. The two tables are
joined to show all the records from the class table and matching records
from the enrollment table.

If no students have entered the class the form will show zero in class
and the max number of open slots. Any students for a class that are active
will show as the number in the class and fewer open slots.

The problem is if all the students entered for a class are not active the
class does not show on the form, even if the class is available for
students. If it is not listed students are not put in the class. What I want
to show is that the class has zero students active with max slots open.



Any suggestions are appreciated.



This is the SQL from the query grid:



SELECT ClassListQuery.ClassClassList, Count(Enrollment.classid) AS
CountOfclassid, Enrollment.Active, ClassListQuery.ActiveClassList

FROM ClassListQuery LEFT JOIN Enrollment ON ClassListQuery.idClassList =
Enrollment.classid

GROUP BY ClassListQuery.ClassClassList, Enrollment.Active,
ClassListQuery.ActiveClassList

HAVING (((Enrollment.Active)="yes") AND
((ClassListQuery.ActiveClassList)="active"));

 

[Marshall Barton]

I have a continuous form that keeps track of how many students are in a
class and how many open slots are left for each class. The two tables are
joined to show all the records from the class table and matching records
from the enrollment table.

If no students have entered the class the form will show zero in class
and the max number of open slots. Any students for a class that are active
will show as the number in the class and fewer open slots.

The problem is if all the students entered for a class are not active the
class does not show on the form, even if the class is available for
students. If it is not listed students are not put in the class. What I want
to show is that the class has zero students active with max slots open.

This is the SQL from the query grid:

SELECT ClassListQuery.ClassClassList, Count(Enrollment.classid) AS
CountOfclassid, Enrollment.Active, ClassListQuery.ActiveClassList

FROM ClassListQuery LEFT JOIN Enrollment ON ClassListQuery.idClassList =
Enrollment.classid

GROUP BY ClassListQuery.ClassClassList, Enrollment.Active,
ClassListQuery.ActiveClassList

HAVING (((Enrollment.Active)="yes") AND
((ClassListQuery.ActiveClassList)="active"));

The first thing to do is to change HAVING to WHERE.

If that does not help, try changing the Active field from
GroupBy to Where and see what results you get..

 

[Parker]

Thanks for the reply Marshall
Unfortunately there is no change. I guess having the active field
filtered for 'yes' prevents me for showing the unused classes.

 

[Marshall Barton]

Ahh, I think I see now. You're right, if you filter out the
inactive records then they won't show up in the form.

If you want the inactive records to show up in the form but
only count the active records try changing the query to:

SELECT ClassListQuery.ClassClassList,
ClassListQuery.ActiveClassList,
Abs(Sum(Enrollment.Active = "yes")) AS
CountOfclassid
FROM ClassListQuery LEFT JOIN Enrollment
ON ClassListQuery.idClassList = Enrollment.classid
GROUP BY ClassListQuery.ClassClassList,
ClassListQuery.ActiveClassList;

If you do not wanr the inactive records to appear in the
form, try something more like:

SELECT ClassListQuery.ClassClassList,
ClassListQuery.ActiveClassList,
Count(Actives.classid) AS CountOfclassid
FROM ClassListQuery
LEFT JOIN (SELECT Enrollment.classid
FROM Enrollment
WHERE Enrollment.Active = "yes") As Actives

 

[Parker]

I'm leaving work now. I quickly tried both suggestions and they both seemed
to showed me the classes I want to see. I'll look more closely tomorrow.

Thank you.

 

[Parker]

It worked. There were a lot of fields I didn't put in my post. I spent all
day configuring the query to get it work with the form. It seems using SQL
is more flexible then the query grid. Thank you.

I'm leaving work now. I quickly tried both suggestions and they both
seemed to showed me the classes I want to see. I'll look more closely
tomorrow.

Thank you.

 

[Marshall Barton]

It worked. There were a lot of fields I didn't put in my post. I spent all
day configuring the query to get it work with the form.

It good to hear that it's working.

It seems using SQL is more flexible then the query grid.

Well, Duh! The query grid is just a limited, "friendly"
user interface for the information needed to create some
simple SQL statements for you. It can deal with a few non
trivial things, but in my opinion, most of those things are
easier to do and understand in SQL view than remembering how
to go through the contortions required in the grid. To be
fair, creating simple queries is quicker than typing it all
out in SQL view and I often use the grid to create the
general stuff and then switch to SQL view to do the rest of
it.

 

[Parker]

Can you suggest a book or website to learn the SQL?

 

[Douglas J. Steele]

SQL Queries for Mere Mortals, 2nd edition, by John Viescas and Michael
Hernadez

http://www.viescas.com/Info/books.htm#Database Design

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can you suggest a book or website to learn the SQL?

 

[Marshall Barton]

Can you suggest a book or website to learn the SQL?

Not really. I generally pick up what little I need by
checking the MS Jet SQL Reference in Access Help, but my
technical background goes back so far that I don't even
remember where I got the basics.

There are plenty of folks in these news groups that have
recommended books and searching Google Groups should find
several of those posts.
http://groups.google.com/advanced_search?q=&

 

[Parker]

Thank you.

SQL Queries for Mere Mortals, 2nd edition, by John Viescas and Michael
Hernadez

http://www.viescas.com/Info/books.htm#Database Design

 

[Parker]

Thank you.

Not really. I generally pick up what little I need by
checking the MS Jet SQL Reference in Access Help, but my
technical background goes back so far that I don't even
remember where I got the basics.

There are plenty of folks in these news groups that have
recommended books and searching Google Groups should find
several of those posts.
http://groups.google.com/advanced_search?q=&