Query with criteria

[Mani]

Hi! I'm not sure why no one is helping me, but I've been waiting for a reply
since Monday. Can somebody help me out?? Here's what I have: I have a
query and I want it to display items that has null value, so I can determine
what certain service students did not receive. The field is based on a combo
box that's why I'm having some difficulty on this. The combo box is
consisted of these service items: Campus Visit, College Readiness, Financial
Aid Counseling, Tutoring, Academic Counseling, Financial Aid Application, and
Admissions Application. So I want the query to find out which students do
not have Financial Aid Application or Admissions Application; therefore I
want the query to display the students that don't have Admissions Application
or Admissions Application as an entered service. I hope that makes sense.
Please help me!!!!!!!! I've been waiting for answers since Monday. Thanks
in advance!!!!

 

[John Spencer]

Well, I see some responses to your request. What I don't see is you
responding to the responses.

Basic strategy is to create a query that show all the Students that HAVE
received the service and then use that in an unmatched query to identify those
that have not received a specific service.

Without field names and table names it is a bit difficult to give you a more
detailed response.

First query might be something like the following (saved as queryNoService):
SELECT studentID
FROM tblStudentServicesReceived
WHERE ServiceItem in ("Financial Aid Application","Admissions Application")

Second query would use that query and the Students table to get a list of
students that are not in the above query. One problem is that the above query
will identify students that do have either service. If you need students that
are missing either service then the query needs to be modified. (see below)
SELECT StudentID
FROM StudentsTable LEFT JOIN queryNoService
ON StudentsTable.StudentID = queryNoService.StudentID
WHERE queryNoService.StudentID is Null

Student has received both services query (Assumes that student can only get a
service once:
SELECT studentID
FROM tblStudentServicesReceived
WHERE ServiceItem in ("Financial Aid Application","Admissions Application")
GROUP BY StudentID
HAVING Count(*)=2

If a student can receive a service more than once (and you are only looking at
two services) then you can use this.
SELECT studentID
FROM tblStudentServicesReceived
WHERE ServiceItem in ("Financial Aid Application","Admissions Application")
GROUP BY StudentID
HAVING Max(ServiceItem) <> Min(ServiceItem)




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County