Find Breaks in Date Ranges

  • Thread starter Thread starter JWeaver
  • Start date Start date
J

JWeaver

We have employees who are to provide Notes regarding clients on a weekly
basis. I want to be able to produce a report that will indicate the name of
any client where Notes were not submitted. I track these Notes using a Start
Date and End Date.

Right now, I sort the records based on client name and dates and then do a
visual search of the printed pages but as the report gets bigger, this task
will take longer and longer. Is there a way to compare 2 start dates and if
there is more than 7 days between them to return the client name on a report
along with the dates that are missing? If so, it would help me tremendously!

Thanks!!!
 
Is there a way to compare 2 start dates and if there is more than 7 days
between them to return the client name on a report along with the dates that
are missing?
Yes. Post your table structure - table and field names with datatype.
Also sample data.
 
Table Structure:
PPE Date - Date/Time
Advocate Last - Text
Advocate First - Text
Emp # - Text
Pay Rate - Number
First - Text (this is the client name)
Last - Text (client)
Program - Text
Con Hours - Number
Con Date - Date/Time
From - Date/Time (this is the start date of week for Notes)
To - Date/Time (this is the end date of week)
Hours - Number
ID - AutoNumber
Dups OK - Text
Note - Text

Sample Date:
PPE Date - 05/23/2008
Advocate Last - Smith
Advocate First - John
Emp # - 012345
Pay Rate - 14.00
First - Julie
Last - Adams
Program - PAL
Con Hours - 25
Con Date - 07/01/08
From - 05/31/08
To - 06/06/08
Hours - 25
ID - AutoNumber
Dups OK - "Blank"
Note - Worked 25 this week.
 
I used table name JWeaver and first query named JWeaver_1. These give you
the record that is more than 7 days from the previous one.

SELECT Q.[Advocate Last], Q.[Advocate First], Q.From, (SELECT COUNT(*) FROM
JWeaver Q1
WHERE Q1.[Advocate Last] = Q.[Advocate Last]
AND Q1.[Advocate First] = Q.[Advocate First]
AND Q1.From < Q.From)+1 AS Rank
FROM JWeaver AS Q
ORDER BY Q.[Advocate Last], Q.[Advocate First], Q.From;

SELECT JWeaver_1_1.Rank, JWeaver.*
FROM (JWeaver INNER JOIN JWeaver_1 ON (JWeaver.[Advocate First] =
JWeaver_1.[Advocate First]) AND (JWeaver.[Advocate Last] =
JWeaver_1.[Advocate Last])) INNER JOIN JWeaver_1 AS JWeaver_1_1 ON
(JWeaver.From = JWeaver_1_1.From) AND (JWeaver.[Advocate First] =
JWeaver_1_1.[Advocate First]) AND (JWeaver.[Advocate Last] =
JWeaver_1_1.[Advocate Last])
WHERE (((JWeaver_1_1.Rank)=[JWeaver_1].[Rank]+1) AND
((DateDiff("d",[JWeaver_1].[From],[JWeaver_1_1].[From]))>7));

This will give you the records that is followed by more than 7 days ---
SELECT JWeaver_1_1.Rank, JWeaver.*
FROM (JWeaver INNER JOIN JWeaver_1 ON (JWeaver.From = JWeaver_1.From) AND
(JWeaver.[Advocate First] = JWeaver_1.[Advocate First]) AND
(JWeaver.[Advocate Last] = JWeaver_1.[Advocate Last])) INNER JOIN JWeaver_1
AS JWeaver_1_1 ON (JWeaver.[Advocate First] = JWeaver_1_1.[Advocate First])
AND (JWeaver.[Advocate Last] = JWeaver_1_1.[Advocate Last])
WHERE (((JWeaver_1_1.Rank)=[JWeaver_1].[Rank]+1) AND
((DateDiff("d",[JWeaver_1].[From],[JWeaver_1_1].[From]))>7));

You can easily create your own to pull the latest records that are more than
7 days old.
 
Back
Top