Compare date to previous date to see if 6 weeks have past

[DougNWa]

I am working on a client appointment query with Client Id, name in one table,
and Appointment date, status, notes, client id for indexing in the
Appointment Table.

I need to make the query compare appointment date from most current to the
previous to see if 6 more weeks have past, if so check the status to see if
its pending, cancelled or rescheduled.

I've played with the DateDiff function and it helps some, and read some were
that I might need to have the same two tables in the query twice to compare
one date to the previous. So far I haven't mastered how best to do this
(feel like I chasing my tail). I like to have it not do the query if there's
one date, do to it being the first appointment.
Thanks in advance.

 

[John Spencer]

QueryA:
Select ClientID, Max([Appointment Date] as LatestAppointment
FROM [YourTable]
GROUP BY ClientID

QueryB:
SELECT ClientID, Max([Appointment Date] as PriorAppointment
FROM [YourTable] as A INNER JOIN QueryA as Qa
ON A.ClientID = Qa.ClientID
AND A.[Appointment Date] < Qa.LatestAppointment
GROUP BY ClientID

QueryC:
SELECT QueryA.ClientID, LatestAppointment,
QueryB.PriorAppointment
FROM QueryA INNER JOIN QueryB
ON QueryA.ClientID = QueryB.ClientID
WHERE DateDiff("d",PriorAppointment,LatestAppointment)>=42





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[DougNWa]

Thanks folks

I tried the idea, but MS Access prompts me for the PreviousDate, which I not
sure how to provide.

More help is a preciated. Also I am rusty (over 6 years since I did an
Access application).

Thanks

Hi -

Try IsScheduled: DateDiff("d", [PreviousDate], [CurrentDate])
and..in the Criteria Cell

= 42

HTH - Bob

I am working on a client appointment query with Client Id, name in one table,
and Appointment date, status, notes, client id for indexing in the
Appointment Table.

I need to make the query compare appointment date from most current to the
previous to see if 6 more weeks have past, if so check the status to see if
its pending, cancelled or rescheduled.

I've played with the DateDiff function and it helps some, and read some were
that I might need to have the same two tables in the query twice to compare
one date to the previous. So far I haven't mastered how best to do this
(feel like I chasing my tail). I like to have it not do the query if there's
one date, do to it being the first appointment.
Thanks in advance.

 

[DougNWa]

Hi John, I sort of understand what you have provided but not sure how to
implement the code into a query.

I have a table of appointments that save the field AppointDate in my
Appointment Table, with a 1 to many link to my Client Table

how would I code the query to know what the previous AppointDate is to be
able to compare with the second ...

QueryA:
Select ClientID, Max([Appointment Date] as LatestAppointment
FROM [YourTable]
GROUP BY ClientID

QueryB:
SELECT ClientID, Max([Appointment Date] as PriorAppointment
FROM [YourTable] as A INNER JOIN QueryA as Qa
ON A.ClientID = Qa.ClientID
AND A.[Appointment Date] < Qa.LatestAppointment
GROUP BY ClientID

QueryC:
SELECT QueryA.ClientID, LatestAppointment,
QueryB.PriorAppointment
FROM QueryA INNER JOIN QueryB
ON QueryA.ClientID = QueryB.ClientID
WHERE DateDiff("d",PriorAppointment,LatestAppointment)>=42





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I am working on a client appointment query with Client Id, name in one table,
and Appointment date, status, notes, client id for indexing in the
Appointment Table.

I need to make the query compare appointment date from most current to the
previous to see if 6 more weeks have past, if so check the status to see if
its pending, cancelled or rescheduled.

I've played with the DateDiff function and it helps some, and read some were
that I might need to have the same two tables in the query twice to compare
one date to the previous. So far I haven't mastered how best to do this
(feel like I chasing my tail). I like to have it not do the query if there's
one date, do to it being the first appointment.
Thanks in advance.

 

[DougNWa]

Hi John, I sort of understand what you have provided but not sure how to
implement the code into a query.

I have a table of appointments that save the field AppointDate in my
Appointment Table, with a 1 to many link to my Client Table

how would I code the query to know what the previous AppointDate is to be
able to compare with the second ...

http://i298.photobucket.com/albums/mm256/DougNWa/MSAccessQuery-01.jpg

(was trying to show a screenshot of what I did so far) not sure if we can
use tags here, didnt seem to work the first time.

I am almost needing a step by step quide.

I'd appreciate any help in getting John's idea to work.

[QUOTE]
[QUOTE="John Spencer"]
QueryA:
Select ClientID, Max([Appointment Date] as LatestAppointment
FROM [YourTable]
GROUP BY ClientID

QueryB:
SELECT ClientID, Max([Appointment Date] as PriorAppointment
FROM [YourTable] as A INNER JOIN QueryA as Qa
ON A.ClientID = Qa.ClientID
AND A.[Appointment Date] < Qa.LatestAppointment
GROUP BY ClientID

QueryC:
SELECT QueryA.ClientID, LatestAppointment,
QueryB.PriorAppointment
FROM QueryA INNER JOIN QueryB
ON QueryA.ClientID = QueryB.ClientID
WHERE DateDiff("d",PriorAppointment,LatestAppointment)>=42





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

[QUOTE]
I am working on a client appointment query with Client Id, name in one table,
and Appointment date, status, notes, client id for indexing in the
Appointment Table.

I need to make the query compare appointment date from most current to the
previous to see if 6 more weeks have past, if so check the status to see if
its pending, cancelled or rescheduled.

I've played with the DateDiff function and it helps some, and read some were
that I might need to have the same two tables in the query twice to compare
one date to the previous. So far I haven't mastered how best to do this
(feel like I chasing my tail). I like to have it not do the query if there's
one date, do to it being the first appointment.
Thanks in advance.
[/QUOTE]
[/QUOTE][/QUOTE]

 

[Bob Barrows [MVP]]

This is going to be complex, requiring both a correlated subquery in the
SELECT clause and a subquery join in the FROM clause that needs to be
done in SQL View. Something like this (untested):

select cur.clientid, cur.status, (
select max(t.[Appointment date] From
Appointment t where clientid=cur.clientid and
t.[Appointment date] < latest.CurrentDate
group by clientid
) as PreviousDate
from Appointment cur join (
select clientid, max([Appointment date] CurrentDate
from Appointment
group by clientid) as latest
on cur.clientid=latest.clientid and
cur.[Appointment date]=latest.CurrentDate
WHERE cur.[Appointment date] > DateAdd("mm",6,[PreviousDate])

Thanks folks

I tried the idea, but MS Access prompts me for the PreviousDate,
which I not sure how to provide.

More help is a preciated. Also I am rusty (over 6 years since I did
an Access application).

Thanks

Hi -

Try IsScheduled: DateDiff("d", [PreviousDate], [CurrentDate])
and..in the Criteria Cell

= 42

HTH - Bob

I am working on a client appointment query with Client Id, name in
one table, and Appointment date, status, notes, client id for
indexing in the Appointment Table.

I need to make the query compare appointment date from most current
to the previous to see if 6 more weeks have past, if so check the
status to see if its pending, cancelled or rescheduled.

I've played with the DateDiff function and it helps some, and read
some were that I might need to have the same two tables in the
query twice to compare one date to the previous. So far I haven't
mastered how best to do this (feel like I chasing my tail). I like
to have it not do the query if there's one date, do to it being the
first appointment.
Thanks in advance.

 

[DougNWa]

Thanks Bob,
Realizing I need to rename a few items, is the cur. in cur.status a sql
command (sorry I liking to get familiar with this coding and wasn't sure
where's a good reference for options in making Query statements).

Also the current project in is MS Access 2003, though I am testing in 2007
at the moment, until I get to my other machine with the 2003 version.

This is going to be complex, requiring both a correlated subquery in the
SELECT clause and a subquery join in the FROM clause that needs to be
done in SQL View. Something like this (untested):

select cur.clientid, cur.status, (
select max(t.[Appointment date] From
Appointment t where clientid=cur.clientid and
t.[Appointment date] < latest.CurrentDate
group by clientid
) as PreviousDate
from Appointment cur join (
select clientid, max([Appointment date] CurrentDate
from Appointment
group by clientid) as latest
on cur.clientid=latest.clientid and
cur.[Appointment date]=latest.CurrentDate
WHERE cur.[Appointment date] > DateAdd("mm",6,[PreviousDate])

Thanks folks

I tried the idea, but MS Access prompts me for the PreviousDate,
which I not sure how to provide.

More help is a preciated. Also I am rusty (over 6 years since I did
an Access application).

Thanks

Hi -

Try IsScheduled: DateDiff("d", [PreviousDate], [CurrentDate])
and..in the Criteria Cell
= 42

HTH - Bob

DougNWa wrote:
I am working on a client appointment query with Client Id, name in
one table, and Appointment date, status, notes, client id for
indexing in the Appointment Table.

I need to make the query compare appointment date from most current
to the previous to see if 6 more weeks have past, if so check the
status to see if its pending, cancelled or rescheduled.

I've played with the DateDiff function and it helps some, and read
some were that I might need to have the same two tables in the
query twice to compare one date to the previous. So far I haven't
mastered how best to do this (feel like I chasing my tail). I like
to have it not do the query if there's one date, do to it being the
first appointment.
Thanks in advance.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Thanks Bob,
Realizing I need to rename a few items, is the cur. in cur.status a
sql command (sorry I liking to get familiar with this coding and
wasn't sure where's a good reference for options in making Query
statements).

No, it's an alias. I've basically given the Appointment table in the
FROM clause a short name to refer to it by.
I assigned the alias in this line:

from Appointment cur join (

It would have been easier for you if I had used the optional As keyword:

from Appointment As cur join (

There are several places I left out the As - let me put them all in:

select cur.clientid, cur.status, (
select max(t.[Appointment date] From
Appointment as t where t.clientid=cur.clientid and
t.[Appointment date] < latest.CurrentDate
group by clientid
) as PreviousDate
from Appointment as cur join (
select clientid, max([Appointment date] as CurrentDate
from Appointment
group by clientid) as latest
on cur.clientid=latest.clientid and
cur.[Appointment date]=latest.CurrentDate
WHERE cur.[Appointment date] > DateAdd("mm",6,[PreviousDate])

Also the current project in is MS Access 2003, though I am testing in
2007 at the moment, until I get to my other machine with the 2003
version.

The only version-specific problem would be if you were using A97.

 

[John Spencer]

What I proposed was a series of three queries.

First Query (gets latest appointment per client) saved as FirstApt
-- Based on the table Appointment
-- Add ClientID and AppointDate as the fields
-- Select View: Totals from the menu
-- Change Group By to MAX under AppointDate
-- Add an alias for the date by typing LatestAppointment: in front of the
field name

Second query (gets the next to last appointment) saved as PriorApt
-- Based on Appointment table and first query
-- Join AppointmentTable.ClientID to FirstApt.ClientID
-- Join AppointmentTable.AppointDate to FirstApt.LatestAppointment
-- add ClientID and AppointDate from the Appointment table
-- Select View: Totals from the menu
-- Change Group By to MAX under AppointDate
-- Add an alias for the date by typing PriorAppointment: in front of the field
name
-- Select View: SQL View from the menu
-- Find and change Appointment.AppointDate = FirstApt.LatestAppointment to
Appointment.AppointDate < FirstApt.LatestAppointment


Third query (gets only those where at least 42 days have elapsed between the
two appointments
-- Use the two queries above
-- join them on ClientID
-- Select the fields you want to see
-- add the following as a calculated field
DateDiff("d",PriorAppointment,LatestAppointment)
-- Set the criteria under the calculated field to >=42


FirstApt:
Select ClientID, Max([AppointDate] as LatestAppointment
FROM [Appointment]
GROUP BY ClientID

PriorApt:
SELECT Appointment.ClientID, Max([AppointDate] as PriorAppointment
FROM Appointment INNER JOIN FirstApt
ON Appointment.ClientID = FirstApt.ClientID
AND Appointment.[AppointDate] < FirstApt.LatestAppointment
GROUP BY ClientID

QueryC:
SELECT FirstApt.ClientID, LatestAppointment,
PriorApt.PriorAppointment
FROM FirstAptINNER JOIN PriorApt
ON FirstApt.ClientID = PriorApt.ClientID
WHERE DateDiff("d",PriorAppointment,LatestAppointment)>=42

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hi John, I sort of understand what you have provided but not sure how to
implement the code into a query.

I have a table of appointments that save the field AppointDate in my
Appointment Table, with a 1 to many link to my Client Table

how would I code the query to know what the previous AppointDate is to be
able to compare with the second ...

http://i298.photobucket.com/albums/mm256/DougNWa/MSAccessQuery-01.jpg

(was trying to show a screenshot of what I did so far) not sure if we can
use tags here, didnt seem to work the first time.

I am almost needing a step by step quide.

I'd appreciate any help in getting John's idea to work.

[QUOTE]
[QUOTE="John Spencer"]
QueryA:
Select ClientID, Max([Appointment Date] as LatestAppointment
FROM [YourTable]
GROUP BY ClientID

QueryB:
SELECT ClientID, Max([Appointment Date] as PriorAppointment
FROM [YourTable] as A INNER JOIN QueryA as Qa
ON A.ClientID = Qa.ClientID
AND A.[Appointment Date] < Qa.LatestAppointment
GROUP BY ClientID

QueryC:
SELECT QueryA.ClientID, LatestAppointment,
QueryB.PriorAppointment
FROM QueryA INNER JOIN QueryB
ON QueryA.ClientID = QueryB.ClientID
WHERE DateDiff("d",PriorAppointment,LatestAppointment)>=42





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


DougNWa wrote:
I am working on a client appointment query with Client Id, name in one table,
and Appointment date, status, notes, client id for indexing in the
Appointment Table.

I need to make the query compare appointment date from most current to the
previous to see if 6 more weeks have past, if so check the status to see if
its pending, cancelled or rescheduled.

I've played with the DateDiff function and it helps some, and read some were
that I might need to have the same two tables in the query twice to compare
one date to the previous. So far I haven't mastered how best to do this
(feel like I chasing my tail). I like to have it not do the query if there's
one date, do to it being the first appointment.
Thanks in advance.
[/QUOTE][/QUOTE][/QUOTE]