seperate records with total 5 days in a sun-sat week (one each day

[Pandorah]

I have a list of employees where each day of leave taken in a year is
indicated. This type of leave is to be taken in 5 day stretches in one
Sun-Sat week (IE: can't start leave on any day other than monday and must
take leave every day that week until Friday).
I want to seperate any leave that was taken properly from any leave that was
not.

IE: Week of Jan 7-13
employee A took leave on Jan 8,9,10,11,12...this would be ok...and should be
on table A
employee B took leave on Jan 8,9,10,11 only...this would be wrong...and
should be on table B

Is there any way to do this in Access?

Thanks!!!!

 

[John Spencer]

SELECT EmployeeID, IIF(Count(Format(LeaveDate,"yyyyww")) = 5 ,"Good","Bad")
FROM YourTable
GROUP BY EmployeeID

Of course that may error when the week overlaps years so you might have to
force the date to monday.

Format(DateAdd("d", 1-Weekday([LeaveDate],2),[LeaveDate]),"yyyyww")

Another problem would be if a date was a Saturday or Sunday. If you wanted
to eliminate Sat and Sun from the calculation you could do so with

SELECT EmployeeID
, IIF(Count(Format(DateAdd("d",
1-Weekday([LeaveDate],2),[LeaveDate]),"yyyyww")) = 5 ,"Good","Bad")
FROM [YourTable]
WHERE WeekDay([LeaveDate],1 ) in (2,3,4,5,6)
GROUP BY EmployeeID

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

 

[Ken Sheridan]

There is a solution published by Joe Celko for identifying sequences (i.e. in
your case each consecutive block of days on which leave is taken by an
employee). This can be applied here to return the start and end dates of
each sequence of leave days per employee. Assuming a table named Leave with
columns EmployeeID and LeaveDate (comprising the composite primary key of the
table) the query would be:

SELECT L1.EmployeeID, L1.LeaveDate As Starts, MIN(L2.LeaveDate) AS Ends
FROM Leave AS L1, Leave AS L2
WHERE L1.EmployeeID = L1.EmployeeID
AND L1.leaveDate <= L2.LeaveDate
AND NOT EXISTS
(SELECT *
FROM Leave AS L3
WHERE L3.EmployeeID = L1.EmployeeID
AND L3.LeaveDate NOT BETWEEN L1.LeaveDate AND L2.LeaveDate
AND (L3.LeaveDate = L1.LeaveDate - 1
OR L3.LeaveDate = L2.LeaveDate +1))
GROUP BY L1.EmployeeID, L1.LeaveDate;

If this is saved as qryLeaveRanges say, the 'good' sequences can be
identified with:

SELECT EmployeeID, Starts, Ends
FROM qryLeaveRanges
WHERE WEEKDAY(Starts,1)=2
AND WEEKDAY(Ends,1) = 6;

and the 'bad' sequences with:

SELECT EmployeeID, Starts, Ends
FROM qryLeaveRanges
WHERE WEEKDAY(Starts,1) <> 2
OR WEEKDAY(Ends,1) <> 6;

Always keep the data together in the one table and use queries to identify
the sequences. Do not separate the good and bad sequences into separate
tables. That leaves the database at risk of inconsistent data being entered,
whereas with the one Leave table this is prevented. You can also include a
Validation Rule on the LeaveDate column to prevent a Saturday of Sunday date
being entered:

Weekday([LeaveDate],2) Not In (6,7)

Ken Sheridan
Stafford, England