Calculating Difference between closest two dates

[joe789]

Hi Folks,

I have been working on this problem for some time; and would greatly appreciate any help:

I have a database which displays individuals and their dates of service (MM/DD/YY) No one individual can have more than 1 date of service on the same day.

As I look thru the database, I notice that some individuals have had several hundred dates of service while others have had only one date of service; this further complexes the problem.

I am trying to find a way to find out which individuals have had a 90-day lapse in their dates of service (this is for a non-for-profit governmental health care agency). For instance, if someone has had 400 appointments, I need a way to compare that individuals first appointment with their second appointment, then their second appointment with their third appointment, then their third appointment with their fourth appointment ..... then their 399th appointment with their 400th appointment to flag any individual that has had a 90-day lapse in service between appointments.

Thank you for your help.

Joe

 

[Jon K]

Try this two queries. It would be easier to directly type/paste each in the SQL View of a new query (replacing with the correct table name and field names in the first query.)

qryAllRecords:-
SELECT [IndividualID], [DatesOfService], DateDiff("d",(Select Max([DatesOfService]) from [TableName] where [IndividualID]=a.[IndividualID] and [DatesOfService] < a.[DatesOfService]), [DatesOfService])-1 AS Day_Lapse
FROM [TableName] AS a
ORDER BY [IndividualID], [DatesOfService];


qryDayLapse90:-
SELECT *
FROM qryAllRecords
WHERE Day_Lapse>=90;


The first query displays the Day_Lapse between the records. The second query pulls from the first query those Day_Lapse >=90.

The second query may contain more than one records for an individual. You can pull a unique list of individuals with a third query:-

SELECT Distinct [IndividualID]
FROM qryDayLapse90


Note. The queries assumes there is a Day_Lapse of 0 between two consecutive days such as 10/13/02 and 10/14/02. If you need to consider two consecutive days as a Day_Lapse of 1, just remove the -1 from the first query.

Hope it helps.

 

[joe789]

Thank you very much!