Now I really am confused - display records with no activity during a date range.

[Dennis]

I am no Access expert and I have managed to really step in it this
time. Is it really that difficult to create a query that shows
Student_Ids from the table Student_Data for those students who did not
receive any services during a given time frame? In this case,
08/01/2003 through 01/31/2004. Can someone please help me find a way
to do this? Some of the fields in this table: Student_ID,
Student_Name, Certificate_Date, Service_Desc, and most important:
Service_Date.
*
I have tried a number of things including looking in the archives of
this newsgroup. I'm sure this topic has been discussed somewhere
before. (There I go assuming again.) I probably just don't know how to
word the question. I keep coming up with student records that had
services that did not occur during the time period, but not records of
students who had NO services during the time period.
*
I have asked another question about this student db recently and got
some good responses, unfortunately it isn't helping for this
situation. So I am hoping that I can impose one last time for help.
I promise not to ask any more. PLEASE! By the way, I am using A2K.

Thank you very much!!

 

[Randy]

try something like

"SELECT Student_Ids, Service_Date from Student_Data where Service_Date
Not Between #08/01/2003# AND #01/31/2004#"

Should work

 

[Dirk Goldgar]

I am no Access expert and I have managed to really step in it this
time. Is it really that difficult to create a query that shows
Student_Ids from the table Student_Data for those students who did not
receive any services during a given time frame? In this case,
08/01/2003 through 01/31/2004. Can someone please help me find a way
to do this? Some of the fields in this table: Student_ID,
Student_Name, Certificate_Date, Service_Desc, and most important:
Service_Date.
*
I have tried a number of things including looking in the archives of
this newsgroup. I'm sure this topic has been discussed somewhere
before. (There I go assuming again.) I probably just don't know how to
word the question. I keep coming up with student records that had
services that did not occur during the time period, but not records of
students who had NO services during the time period.
*
I have asked another question about this student db recently and got
some good responses, unfortunately it isn't helping for this
situation. So I am hoping that I can impose one last time for help.
I promise not to ask any more. PLEASE! By the way, I am using A2K.

Thank you very much!!

Let me restate the question, to make sure we're in agreement. As I
understand it, you want to extract the Student_IDs of those students who
have no records in Student_Data with Service_Date between 08/01/2003 and
01/31/2004. I assume you only want distinct Student_IDs; that is, in
the results returned, each Student_ID should be occur only once. This
would be a bit cleaner to write if we had a Students table to work with,
having one record per Student. As it is, with only the Student_Data
table to draw from, I can only extract Students who have at least one
record in the table. I can't return any student who isn't in the table
at all.

Anyway, I think this would do it for you, though Access won't process it
very efficiently:

SELECT DISTINCT Student_ID FROM Student_Data
WHERE StudentID NOT IN
(SELECT T.Student_ID FROM Student_Data AS T
WHERE T.Service_Date Between #08/01/2003# AND #01/31/2004#);

Another, probably more efficient (if also more obscure) variation would
be

SELECT DISTINCT A.Student_ID
FROM
Student_Data A
LEFT JOIN
[SELECT Student_ID FROM Student_Data
WHERE Service_Date Between #08/01/2003# AND #01/31/2004#]. B
ON A.Student_ID = B.Student_ID
WHERE B.Student_ID Is Null;

I'm no great SQL maven, and I haven't checked that to see if it's
altogether correct, but it's something close.

 

[John Vinson]

I am no Access expert and I have managed to really step in it this
time. Is it really that difficult to create a query that shows
Student_Ids from the table Student_Data for those students who did not
receive any services during a given time frame? In this case,
08/01/2003 through 01/31/2004. Can someone please help me find a way
to do this? Some of the fields in this table: Student_ID,
Student_Name, Certificate_Date, Service_Desc, and most important:
Service_Date.

A "NOT IN" query will do this (though I confess it may run rather
slowly!)

Create a Query based on your table displaying the fields you want to
see. On the Student_ID field put a criterion of

NOT IN(SELECT SubQ.Student_ID FROM Student_Data AS SubQ
WHERE SubQ.ServiceDate BETWEEN #08/01/2003# AND #01/31/2004#)

Open the query datasheet and you should see only those students who
received no services in that date range.

I have asked another question about this student db recently and got
some good responses, unfortunately it isn't helping for this
situation. So I am hoping that I can impose one last time for help.
I promise not to ask any more. PLEASE! By the way, I am using A2K.

You're welcome to come back as often as you wish! Please, remember
that the folks who answer are volunteers - we're here because we
choose to be, not because we have to do so; sometimes it just happens
that a question doesn't get an answer, or a relevant answer, because
none of the volunteers were aware that there was still a problem! (If
I'm busy I'll skip threads with answers - and might not realize that
they are not helpful answers - and that may be the case even if it was
my OWN answer that wasn't helpful!)

 

[John Vinson]

try something like

"SELECT Student_Ids, Service_Date from Student_Data where Service_Date
Not Between #08/01/2003# AND #01/31/2004#"

That won't actually work - it will show Student_ID's for people who DO
have service dates in that range, but it just won't show those dates.

 

[Van T. Dinh]

That won't actually work - it will show Student_ID's for people who DO
have service dates in that range, but it just won't show those dates.

I think you meant:

That won't actually work - it will show Student_ID's for people who DO
have service dates NOT in that range

???

Cheers
Van

 

[John Vinson]

I think you meant:

Well, that was ambiguous wasn't it! <g>

The NOT BETWEEN critera will simply select all StudentID's for which
there exist dates outside the range. The original question - as I read
it anyway - wanted to specifically EXCLUDE students who had dates
within that range, whether they had other dates or not. This criterion
will not do so - if student 123 had data from 2/4/2004 and from
1/14/2004, the BETWEEN query would return only the latter of the two
dates but *would* return that student.

 

[Pieter Wijnen]

Try not exists
SELECT STUDENTID FROM STUDENT A
WHERE NOT EXISTS (SELECT 'X' FROM STUDENTREQUEST B
WHERE B.STUDENTID = A.STUDENTID
AND B.SERVICEDATE BETWEEN <FROMDATE> AND <TODATE)

HTH
Pieter