selecting records between 2 dates

[pb]

here goes:

building a patient reminder system with MS access 2000 on backend. need
correct syntax for following problems.
Basically, 1 patient can have many
appointments, 1:M relationship. 2 tables, PATIENTS & SCHEDULED. Each
patient is uniquely defined as PT_ID, which is autonumbered and so is each
appointment; SCHEDULE_ID.
-Patient is recalled on a specific date called RECALLDUEDATE
-14 days prior to that RECALLDUEDATE a reminder is sent out.
-When an appointment is scheduled...it is saved as START_TIME, which is the
start time of the appointment.

I can query all the patients that are due in 2 weeks prior to that date using:

SELECT PATIENTS.*, SCHEDULED.*
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE>Now() And
DATEDIFF('d',SCHEDULED.RECALLDUEDATE,Now())<=14

HOWEVER, and here lies the problem: if the patient has scheduled an
appointment between last appointment and expected due date(RECALLDUEDATE),
then i want him omitted from the query. Any suggestions? If i'm going
about this the wrong way....any advice on how to design it better is
appreciated.

Some sample Data:

PATIENTS TABLE:
PT_ID: 45
LAST_NAME: USHER
PT_ID: 54
LAST_NAME: SMITH

SCHEDULED TABLE:
SCHEDULE_ID:225
START_TIME: 26/05/2005 08:00
RECALLDUEDATE: 24/08/2005
PT_ID: 45

SCHEDULE_ID: 229
START_TIME: 20/08/2005 08:00:00
RECALLDUEDATE:
PT_ID: 45

SCHEDULE_ID: 231
START_TIME: 21/05/2005 08:00:00
RECALLDUEDATE: 25/08/2005
PT_ID: 54

Expected results: PT_ID: 54 LAST_NAME: SMITH (I think, so it should select
the patient who is due in within the next 14 days, BUT has not scheduled an
appointment since the last one)

THis is my latest SQL query, however, it throws up an error in access
saying, "only 1 record may be selected by the subquery". So it works if
there is only 1 patient who is on the database.

SELECT PATIENTS.*, SCHEDULED.*
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE>Now() And
DATEDIFF('d',SCHEDULED.RECALLDUEDATE,Now())<=14 And (SELECT COUNT(*) FROM
SCHEDULED WHERE SCHEDULED.PT_ID=PATIENTS.PT_ID And SCHEDULED.START_TIME>Now()
AND SCHEDULED.START_TIME<(SELECT SCHEDULED.RECALLDUEDATE FROM SCHEDULED WHERE
SCHEDULED.RECALLDUEDATE>NOW()))=0

 

[peregenem]

THis is my latest SQL query, however, it throws up an error in access
saying, "only 1 record may be selected by the subquery". So it works if
there is only 1 patient who is on the database.

SELECT PATIENTS.*, SCHEDULED.*
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE>Now() And
DATEDIFF('d',SCHEDULED.RECALLDUEDATE,Now())<=14 And (SELECT COUNT(*) FROM
SCHEDULED WHERE SCHEDULED.PT_ID=PATIENTS.PT_ID And SCHEDULED.START_TIME>Now()
AND SCHEDULED.START_TIME<(SELECT SCHEDULED.RECALLDUEDATE FROM SCHEDULED WHERE
SCHEDULED.RECALLDUEDATE>NOW()))=0

Great post: structure, test data, expected results and your attempted
SQL solution.

Here's my attempted solution:

SELECT *
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID = SCHEDULED.PT_ID
And SCHEDULED.RECALLDUEDATE > Now()
And DATEDIFF('d', SCHEDULED.RECALLDUEDATE, Now()) <= 14
And (
SELECT COUNT(*) FROM SCHEDULED AS T2
WHERE T2.PT_ID = SCHEDULED.PT_ID
And T2.START_TIME > SCHEDULED.START_TIME
And T2.START_TIME < SCHEDULED.RECALLDUEDATE
And T2.RECALLDUEDATE IS NULL
) = 0;

 

[peregenem]

Possibly better:

SELECT *
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID = SCHEDULED.PT_ID
And SCHEDULED.RECALLDUEDATE > Now()
And DATEDIFF('d', SCHEDULED.RECALLDUEDATE, Now()) <= 14
And NOT EXISTS (SELECT * FROM SCHEDULED AS T2
WHERE T2.PT_ID = SCHEDULED.PT_ID
And T2.START_TIME > SCHEDULED.START_TIME
And T2.START_TIME < SCHEDULED.RECALLDUEDATE
And T2.RECALLDUEDATE IS NULL
);

 

[Chaim]

Can't the last subselect return more than one record?
SCHEDULED.START_TIME<(SELECT SCHEDULED.RECALLDUEDATE FROM SCHEDULED WHERE
SCHEDULED.RECALLDUEDATE>NOW())

It will select (well, it would if it stood by itself) every record from
SCHEDULED having RECALLDUEDATE > Now(). I would expect more than 1 record to
satisfy this condition.

Are there missing criteria? It seems that there should be something
correlating this to a patient ID in the outer select.

 

[pb]

Thanks for the responses, i have managed to fumble this out....and it
works....so far...

SELECT PATIENTS.*, SCHEDULED.*
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID=SCHEDULED.PT_ID And SCHEDULED.RECALLDUEDATE>Now() And
DATEDIFF('d',SCHEDULED.RECALLDUEDATE,Now())<=14 And (SELECT COUNT(*) FROM
SCHEDULED WHERE SCHEDULED.PT_ID=PATIENTS.PT_ID And SCHEDULED.START_TIME>Now()
AND (DATEDIFF('d', SCHEDULED.START_TIME, Now) <0 OR DATEDIFF('d',
SCHEDULED.START_TIME, Now)>14))=0;

 

[pb]

This worked as well.....thanks!

Great post: structure, test data, expected results and your attempted
SQL solution.

Here's my attempted solution:

SELECT *
FROM PATIENTS, SCHEDULED
WHERE PATIENTS.PT_ID = SCHEDULED.PT_ID
And SCHEDULED.RECALLDUEDATE > Now()
And DATEDIFF('d', SCHEDULED.RECALLDUEDATE, Now()) <= 14
And (
SELECT COUNT(*) FROM SCHEDULED AS T2
WHERE T2.PT_ID = SCHEDULED.PT_ID
And T2.START_TIME > SCHEDULED.START_TIME
And T2.START_TIME < SCHEDULED.RECALLDUEDATE
And T2.RECALLDUEDATE IS NULL
) = 0;

 

[pb]

this worked too!