Calculating Meal Period Violations in Access

[DoveArrow]

I have a table, called "tblStudentSemesterJobHours", which I am using to track the clock in and clock out times for our student workers. The table contains the following fields.

StudentID
Semester
Job
WorkDate
StartTime
EndTime

What I am trying to do now is create a query that can find all student workers who have worked more than five hours in a single day without a minimum of a 30 minute break. If a student has worked more than five hours without that 30 minute break, I want the query to tell me when the student first clocked in and when the student last clocked out.

Let me give you an example. Let's say I have a student who works the following hours:

4/15/2012 8:00 AM - 12:00 PM
4/15/2012 12:15 PM - 3:15 PM
4/15/2012 3:30 PM - 5:00 PM

In this situation, the student has not had a minimum 30 minute break between each of these three shifts. As such, I want a querey that will tell me the first time the student clocked in and the last time the student clocked out.

4/15/2012 8:00 AM - 5:00 PM

After scouring the internet for a few weeks, looking for a solution that might work, I found the following blog post (http://pratchev.blogspot.com/2010/02/refactoring-ranges.html). After fiddling around with it for a while, Icame up with the following.

SELECT T.StudentID, T.Semester, T.WorkDate, Min(T.StartTime) AS StartTime, Max(T.EndTime) AS EndTime
FROM (SELECT A.StudentID, A.Semester, A.WorkDate, A.StartTime, A.EndTime, (Select Min(B.StartTime) From tblStudentSemesterJobHours as B Where B.WorkDate=A.WorkDate AND Not Exists (Select * From tblStudentSemesterJobHours as C Where A.StartTime >= DateAdd('n', 300, B.StartTime ))) AS grp
FROM tblStudentSemesterJobHours AS A) AS T
GROUP BY T.StudentID, T.Semester, T.WorkDate, T.grp
HAVING (((T.Semester)=[Forms]![fqryStudentHours]![Semester]));

Now this query works great if all you have is two entries. The problem is that third entry. Because the start time is more than 5 hours greater than the minimum start time, it pulls the following:

4/15/2012 8:00 AM - 3:15 PM
4/15/2012 3:30 PM - 5:00 PM

Now I've been wracking my brain over this issue for a while trying different combinations of numbers and unfortunately, I just can't get figure out a way to get it to pull all the entries. Since I barely understand how this query works anyway, I thought I should turn the problem over to some people who are a little more savvy than I. Anybody have any ideas?

 

[XPS350]

Op donderdag 23 augustus 2012 01:38:35 UTC+2 schreef DoveArrow het volgende:

I have a table, called "tblStudentSemesterJobHours", which I am using to track the clock in and clock out times for our student workers. The table contains the following fields.



StudentID

Semester

Job

WorkDate

StartTime

EndTime



What I am trying to do now is create a query that can find all student workers who have worked more than five hours in a single day without a minimum of a 30 minute break. If a student has worked more than five hours without that 30 minute break, I want the query to tell me when the student first clocked in and when the student last clocked out.



Let me give you an example. Let's say I have a student who works the following hours:



4/15/2012 8:00 AM - 12:00 PM

4/15/2012 12:15 PM - 3:15 PM

4/15/2012 3:30 PM - 5:00 PM



In this situation, the student has not had a minimum 30 minute break between each of these three shifts. As such, I want a querey that will tell me the first time the student clocked in and the last time the student clockedout.



4/15/2012 8:00 AM - 5:00 PM



After scouring the internet for a few weeks, looking for a solution that might work, I found the following blog post (http://pratchev.blogspot.com/2010/02/refactoring-ranges.html). After fiddling around with it for a while,I came up with the following.



SELECT T.StudentID, T.Semester, T.WorkDate, Min(T.StartTime) AS StartTime, Max(T.EndTime) AS EndTime

FROM (SELECT A.StudentID, A.Semester, A.WorkDate, A.StartTime, A.EndTime,(Select Min(B.StartTime) From tblStudentSemesterJobHours as B Where B.WorkDate=A.WorkDate AND Not Exists (Select * From tblStudentSemesterJobHoursas C Where A.StartTime >= DateAdd('n', 300, B.StartTime ))) AS grp

FROM tblStudentSemesterJobHours AS A) AS T

GROUP BY T.StudentID, T.Semester, T.WorkDate, T.grp

HAVING (((T.Semester)=[Forms]![fqryStudentHours]![Semester]));



Now this query works great if all you have is two entries. The problem isthat third entry. Because the start time is more than 5 hours greater thanthe minimum start time, it pulls the following:



4/15/2012 8:00 AM - 3:15 PM

4/15/2012 3:30 PM - 5:00 PM



Now I've been wracking my brain over this issue for a while trying different combinations of numbers and unfortunately, I just can't get figure out a way to get it to pull all the entries. Since I barely understand how thisquery works anyway, I thought I should turn the problem over to some people who are a little more savvy than I. Anybody have any ideas?

After some trial and error, I ended up with:

SELECT T.StudentID, T.WorkDate, Min(T.StartTime) AS FirstStartTime, Max(T.EndTime) AS LastEndTime
FROM T
GROUP BY T.StudentID, T.WorkDate
HAVING (((Sum(DateDiff("n",[StartTime],[Endtime])))>300) AND ((Max(Val(Nz(DateDiff("n",[EndTime],DLookUp("Starttime","T","Workdate=#" & [Workdate] &"# AND StudentID=" & [StudentID] & " AND StartTime>#" & [EndTime] & "#")),0))))<30))

Peter

 

[DoveArrow]

You are as unto a god! Thank you!

Op donderdag 23 augustus 2012 01:38:35 UTC+2 schreef DoveArrow het volgende:

I have a table, called "tblStudentSemesterJobHours", which I am using to track the clock in and clock out times for our student workers. The tablecontains the following fields.

StudentID

Semester

Job

WorkDate

StartTime

EndTime

What I am trying to do now is create a query that can find all student workers who have worked more than five hours in a single day without a minimum of a 30 minute break. If a student has worked more than five hours without that 30 minute break, I want the query to tell me when the student first clocked in and when the student last clocked out.
Let me give you an example. Let's say I have a student who works the following hours:
4/15/2012 8:00 AM - 12:00 PM
4/15/2012 12:15 PM - 3:15 PM
4/15/2012 3:30 PM - 5:00 PM
In this situation, the student has not had a minimum 30 minute break between each of these three shifts. As such, I want a querey that will tell me the first time the student clocked in and the last time the student clocked out.
4/15/2012 8:00 AM - 5:00 PM
After scouring the internet for a few weeks, looking for a solution that might work, I found the following blog post (http://pratchev.blogspot.com/2010/02/refactoring-ranges.html). After fiddling around with it for a while, I came up with the following.
SELECT T.StudentID, T.Semester, T.WorkDate, Min(T.StartTime) AS StartTime, Max(T.EndTime) AS EndTime
FROM (SELECT A.StudentID, A.Semester, A.WorkDate, A.StartTime, A.EndTime, (Select Min(B.StartTime) From tblStudentSemesterJobHours as B Where B.WorkDate=A.WorkDate AND Not Exists (Select * From tblStudentSemesterJobHours as C Where A.StartTime >= DateAdd('n', 300, B.StartTime ))) AS grp
FROM tblStudentSemesterJobHours AS A) AS T
GROUP BY T.StudentID, T.Semester, T.WorkDate, T.grp
HAVING (((T.Semester)=[Forms]![fqryStudentHours]![Semester]));
Now this query works great if all you have is two entries. The problem is that third entry. Because the start time is more than 5 hours greater than the minimum start time, it pulls the following:
4/15/2012 8:00 AM - 3:15 PM
4/15/2012 3:30 PM - 5:00 PM
Now I've been wracking my brain over this issue for a while trying different combinations of numbers and unfortunately, I just can't get figure out a way to get it to pull all the entries. Since I barely understand how this query works anyway, I thought I should turn the problem over to some people who are a little more savvy than I. Anybody have any ideas?

After some trial and error, I ended up with:



SELECT T.StudentID, T.WorkDate, Min(T.StartTime) AS FirstStartTime, Max(T..EndTime) AS LastEndTime

FROM T

GROUP BY T.StudentID, T.WorkDate

HAVING (((Sum(DateDiff("n",[StartTime],[Endtime])))>300) AND ((Max(Val(Nz(DateDiff("n",[EndTime],DLookUp("Starttime","T","Workdate=#" & [Workdate]& "# AND StudentID=" & [StudentID] & " AND StartTime>#" & [EndTime] & "#")),0))))<30))



Peter