I have a select query which is being used to identify employees who are on vacation or jury duty for more than 3 days in a week. The query can identify and list the employees who are off for the entire week, 5 days, but is not picking up the employees who are off for 3 or 4 days. Here is a copy of the query:
SELECT Att.EmployeeNumber,
Att.DateWeekStarting
FROM tblAttendence AS Att
WHERE (Att.DateWeekStarting
Between Nz([Enter Start Date],Date()-7)
And Nz([Enter End Date],DateAdd("ww",-14,Date())))
And Abs((Att.WorkDay1Reason="JurD-A" Or Att.WorkDay1Reason="VacD-A"
+(Att.WorkDay2Reason="JurD-A"
Or Att.WorkDay2Reason="VacD-A"
+(Att.WorkDay3Reason="JurD-A"
Or Att.WorkDay3Reason="VacD-A"
+(Att.WorkDay4Reason="JurD-A"
Or Att.WorkDay4Reason="VacD-A"
+(Att.WorkDay5Reason="JurD-A"
Or Att.WorkDay5Reason="VacD-A")>=3
ORDER BY Att.EmployeeNumber;
Any help in straightening out this query will be greatly appreciated. Thank you very much for all of the assistance.
SELECT Att.EmployeeNumber,
Att.DateWeekStarting
FROM tblAttendence AS Att
WHERE (Att.DateWeekStarting
Between Nz([Enter Start Date],Date()-7)
And Nz([Enter End Date],DateAdd("ww",-14,Date())))
And Abs((Att.WorkDay1Reason="JurD-A" Or Att.WorkDay1Reason="VacD-A"
+(Att.WorkDay2Reason="JurD-A"
Or Att.WorkDay2Reason="VacD-A"
+(Att.WorkDay3Reason="JurD-A"
Or Att.WorkDay3Reason="VacD-A"
+(Att.WorkDay4Reason="JurD-A"
Or Att.WorkDay4Reason="VacD-A"
+(Att.WorkDay5Reason="JurD-A"
Or Att.WorkDay5Reason="VacD-A")>=3
ORDER BY Att.EmployeeNumber;
Any help in straightening out this query will be greatly appreciated. Thank you very much for all of the assistance.