Find Breaks in Data Ranges - Follow-up

J

JWeaver

NOTE: I posted the following message at the beginning of June but haven't had
the time to try the response until now and was wondering just how do I go
about trying to make this happen? Where do I put the code, in a query or in
a report? Can I just copy and paste the code or do I have to do other things
to it to make it work?
----
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!

Table Structure (Name: Wrap Payroll):
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.
-----
Response by Karl Dewey:
I used table name JWeaver (I changed this to Wrap Payroll) and first query
named JWeaver_1 (Changed to Wrap Payroll_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
Wrap Payroll Q1
WHERE Q1.[Advocate Last] = Q.[Advocate Last]
AND Q1.[Advocate First] = Q.[Advocate First]
AND Q1.From < Q.From)+1 AS Rank
FROM Wrap Payroll AS Q
ORDER BY Q.[Advocate Last], Q.[Advocate First], Q.From;

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

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


Any help is greatly appreciated!!!
 
K

KARL DEWEY

So what is your question now?
Post more than one record for sample data and post horizontally insteads of
vertically like this --
PPE Date Advocate Last Advocate First Emp # Pay Rate First Last Program Con
Hours Con Date From To Hours ID Dups OK Not
5/23/2008 Smith John 12345 14 Julie Adams PAL 25 7/1/2008 5/31/2008 6/6/2008 25 5 Blank Worked 25 this week.
--
KARL DEWEY
Build a little - Test a little


JWeaver said:
NOTE: I posted the following message at the beginning of June but haven't had
the time to try the response until now and was wondering just how do I go
about trying to make this happen? Where do I put the code, in a query or in
a report? Can I just copy and paste the code or do I have to do other things
to it to make it work?
----
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!

Table Structure (Name: Wrap Payroll):
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.
-----
Response by Karl Dewey:
I used table name JWeaver (I changed this to Wrap Payroll) and first query
named JWeaver_1 (Changed to Wrap Payroll_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
Wrap Payroll Q1
WHERE Q1.[Advocate Last] = Q.[Advocate Last]
AND Q1.[Advocate First] = Q.[Advocate First]
AND Q1.From < Q.From)+1 AS Rank
FROM Wrap Payroll AS Q
ORDER BY Q.[Advocate Last], Q.[Advocate First], Q.From;

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

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


Any help is greatly appreciated!!!
Click to expand...
 
J

JWeaver

My question is - where/how do I put the code you gave me?

Sample Data:
PPE Date Advocate Last Advocate First Emp # Pay Rate First Last
Program Con Hours Con Date From To Hours ID Dups OK Note
05/23/2008 Smith John 12345 14 Julie Adams PAL 25 07/01/08 05/31/08
06/06/08 25 AutoNumber "Blank" Worked 25 this week.
05/23/2008 Smith John 12345 14 Julie Adams PAL 25 07/01/08 6/7/2008
06/13/08 25 AutoNumber "Blank" Worked 25 this week.
05/30/2008 Smith John 12345 14 Julie Adams PAL 25 07/01/08 6/21/2008
06/27/08 25 AutoNumber "Blank" Worked 25 this week.

Thanks for taking the time to try to help me!
--
JWeaver


KARL DEWEY said:
So what is your question now?
Post more than one record for sample data and post horizontally insteads of
vertically like this --
PPE Date Advocate Last Advocate First Emp # Pay Rate First Last Program Con
Hours Con Date From To Hours ID Dups OK Note
5/23/2008 Smith John 12345 14 Julie Adams PAL 25 7/1/2008 5/31/2008 6/6/2008 25 5 Blank Worked 25 this week.
--
KARL DEWEY
Build a little - Test a little


JWeaver said:
NOTE: I posted the following message at the beginning of June but haven't had
the time to try the response until now and was wondering just how do I go
about trying to make this happen? Where do I put the code, in a query or in
a report? Can I just copy and paste the code or do I have to do other things
to it to make it work?
----
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!

Table Structure (Name: Wrap Payroll):
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.
-----
Response by Karl Dewey:
I used table name JWeaver (I changed this to Wrap Payroll) and first query
named JWeaver_1 (Changed to Wrap Payroll_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
Wrap Payroll Q1
WHERE Q1.[Advocate Last] = Q.[Advocate Last]
AND Q1.[Advocate First] = Q.[Advocate First]
AND Q1.From < Q.From)+1 AS Rank
FROM Wrap Payroll AS Q
ORDER BY Q.[Advocate Last], Q.[Advocate First], Q.From;

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

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


Any help is greatly appreciated!!!
Click to expand...
Click to expand...
 
K

KARL DEWEY

Create a query by clicking on New in the query window and close the table
selection window without selecting a table. Click on menu VIEW - SQL View,
and paste the first SQL statement in the SQL window. Edit out any hard
returns the posting, copying, and pasting may have added - the only returns
should those that preceed FROM and ORDER BY in the first one. Save the query.
In the second and third the returns preceed FROM and WHERE.

--
KARL DEWEY
Build a little - Test a little


JWeaver said:
My question is - where/how do I put the code you gave me?

Sample Data:
PPE Date Advocate Last Advocate First Emp # Pay Rate First Last
Program Con Hours Con Date From To Hours ID Dups OK Note
05/23/2008 Smith John 12345 14 Julie Adams PAL 25 07/01/08 05/31/08
06/06/08 25 AutoNumber "Blank" Worked 25 this week.
05/23/2008 Smith John 12345 14 Julie Adams PAL 25 07/01/08 6/7/2008
06/13/08 25 AutoNumber "Blank" Worked 25 this week.
05/30/2008 Smith John 12345 14 Julie Adams PAL 25 07/01/08 6/21/2008
06/27/08 25 AutoNumber "Blank" Worked 25 this week.

Thanks for taking the time to try to help me!
--
JWeaver


KARL DEWEY said:
So what is your question now?
Post more than one record for sample data and post horizontally insteads of
vertically like this --
PPE Date Advocate Last Advocate First Emp # Pay Rate First Last Program Con
Hours Con Date From To Hours ID Dups OK Note
5/23/2008 Smith John 12345 14 Julie Adams PAL 25 7/1/2008 5/31/2008 6/6/2008 25 5 Blank Worked 25 this week.
--
KARL DEWEY
Build a little - Test a little


JWeaver said:
NOTE: I posted the following message at the beginning of June but haven't had
the time to try the response until now and was wondering just how do I go
about trying to make this happen? Where do I put the code, in a query or in
a report? Can I just copy and paste the code or do I have to do other things
to it to make it work?
----
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!

Table Structure (Name: Wrap Payroll):
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.
-----
Response by Karl Dewey:
I used table name JWeaver (I changed this to Wrap Payroll) and first query
named JWeaver_1 (Changed to Wrap Payroll_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
Wrap Payroll Q1
WHERE Q1.[Advocate Last] = Q.[Advocate Last]
AND Q1.[Advocate First] = Q.[Advocate First]
AND Q1.From < Q.From)+1 AS Rank
FROM Wrap Payroll AS Q
ORDER BY Q.[Advocate Last], Q.[Advocate First], Q.From;

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

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


Any help is greatly appreciated!!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Database Design/Change Help Requested 2
Use combo box to force a selection in secondary combo boxes? 7
Proper Table Design and Combo Box Help 3
Adding SubForm to Existing Form 1
Adding a Subform to An Existing Form 0
Normalizing Tables and Enter Data in Fields Based on Employee Numb 1
Add a field to Query 2
Sumarize data in Query like in a Pivot Table 7

Top