Interesting (well, for me anyway) Query Question

[Dennis]

Hi all.
I have a db that has student data in it. Particularly, the dates
students received certificates. Table Fields: Student_id,
Certificate_date, Certificate_id, Certification_desc, AdminStaff_id.
What I am trying to retrieve are those student records who did not
receive a certificate in the period from 07/01/2003 through
12/31/2004, but DID receive a certificate from the Admin Staff between
01/01/2004 and 01/31/2004. One catch that has to be reckoned with is
that the student COULD have received one or even several certificates
prior to July 01, 2003.
*
I know most of you are very busy, so any help you could give toward
understanding this would be more than appreciated.
Thanks!
D

 

[George Nicholson]

You don't need a query for this. No one will meet your criteria.

Anyone who didn't receive a certificate between 7/1/03 and 12/31/04 didn't
receive one between 1/1/04 and 1/31/04 either.

 

[MacDermott]

How subqueries like this:

SELECT Student_ID (and any other fields you want) FROM MyTable WHERE
Student_ID IN (SELECT Student_ID FROM MyTable WHERE Certificate_Date BETWEEN
#01/01/2004# AND #01/31/2004#) AND Student_ID NOT IN (SELECT Student_ID FROM
MyTable WHERE Certificate_Date BETWEEN #07/01/2003# AND #12/31/2003#)

NOTE: I'm guessing that you actually meant to end your excluded period in
2003, not 2004.

HTH
- Turtle

 

[Dennis]

Turtle: Thanks for your suggestion - and yes, I did mean to say 2003.

 

[GERTRUDE HAYES]

Turtle: Thanks for your suggestion - and yes, I did mean to say 2003.

"MacDermott" <[email protected]> wrote in message