This is a select query to find a list of employees who have 13 weeks of perfect attendence. Simple right. Holidays are counted as regular days and one or two vacation days or jury duty days are also counted as regular days. Simple right. Here is where it gets tuff, three , four or five vaction days or jury duty days, the week is to be ignored or not counted. I found that Or Exists with an Abs count can get me close. Here is section of my code that I am using:
Or Exists(SELECT Att.EmployeeNumber,
Att.DateWeekStarting
FROM tblAttendence
WHERE ((Abs((Nz(Att.WorkDay1Reason="JurD-A"
Or Nz(Att.WorkDay1Reason="VacD-A")
+(Nz(Att.WorkDay2Reason="JurD-A"
Or Nz(Att.WorkDay2Reason="VacD-A")
+(Nz(Att.WorkDay3Reason="JurD-A"
Or Nz(Att.WorkDay3Reason="VacD-A")
+(Nz(Att.WorkDay4Reason="JurD-A"
Or Nz(Att.WorkDay4Reason="VacD-A")
+(Nz(Att.WorkDay5Reason="JurD-A"
Or Nz(Att.WorkDay5Reason="VacD-A"))=3)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A"
Or Nz(Att.WorkDay1Reason="VacD-A")
+(Nz(Att.WorkDay2Reason="JurD-A"
Or Nz(Att.WorkDay2Reason="VacD-A")
+(Nz(Att.WorkDay3Reason="JurD-A"
Or Nz(Att.WorkDay3Reason="VacD-A")
+(Nz(Att.WorkDay4Reason="JurD-A"
Or Nz(Att.WorkDay4Reason="VacD-A")
+(Nz(Att.WorkDay5Reason="JurD-A"
Or Nz(Att.WorkDay5Reason="VacD-A"))=4)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A"
Or Nz(Att.WorkDay1Reason="VacD-A")
+(Nz(Att.WorkDay2Reason="JurD-A"
Or Nz(Att.WorkDay2Reason="VacD-A")
+(Nz(Att.WorkDay3Reason="JurD-A"
Or Nz(Att.WorkDay3Reason="VacD-A")
+(Nz(Att.WorkDay4Reason="JurD-A"
Or Nz(Att.WorkDay4Reason="VacD-A")
+(Nz(Att.WorkDay5Reason="JurD-A"
Or Nz(Att.WorkDay5Reason="VacD-A"))=5)))
I would like to add this condition to the end, but I'm not sure how. This is what I would like:
Then GetNumWeeks()+1 AND RecCnt+0
Also I would need to know how to reset the GetNumWeeks() to the default when the next EmployeeNumber comes up. I have tryed several different things, but I keep getting syntax errors or not a valid SQL command. Thank you in advance for anyone who assists me.
Or Exists(SELECT Att.EmployeeNumber,
Att.DateWeekStarting
FROM tblAttendence
WHERE ((Abs((Nz(Att.WorkDay1Reason="JurD-A"
Or Nz(Att.WorkDay1Reason="VacD-A"
)+(Nz(Att.WorkDay2Reason="JurD-A"
Or Nz(Att.WorkDay2Reason="VacD-A"
)+(Nz(Att.WorkDay3Reason="JurD-A"
Or Nz(Att.WorkDay3Reason="VacD-A"
)+(Nz(Att.WorkDay4Reason="JurD-A"
Or Nz(Att.WorkDay4Reason="VacD-A"
)+(Nz(Att.WorkDay5Reason="JurD-A"
Or Nz(Att.WorkDay5Reason="VacD-A"
))=3)Or (Abs((Nz(Att.WorkDay1Reason="JurD-A"
Or Nz(Att.WorkDay1Reason="VacD-A"
)+(Nz(Att.WorkDay2Reason="JurD-A"
Or Nz(Att.WorkDay2Reason="VacD-A"
)+(Nz(Att.WorkDay3Reason="JurD-A"
Or Nz(Att.WorkDay3Reason="VacD-A"
)+(Nz(Att.WorkDay4Reason="JurD-A"
Or Nz(Att.WorkDay4Reason="VacD-A"
)+(Nz(Att.WorkDay5Reason="JurD-A"
Or Nz(Att.WorkDay5Reason="VacD-A"
))=4)Or (Abs((Nz(Att.WorkDay1Reason="JurD-A"
Or Nz(Att.WorkDay1Reason="VacD-A"
)+(Nz(Att.WorkDay2Reason="JurD-A"
Or Nz(Att.WorkDay2Reason="VacD-A"
)+(Nz(Att.WorkDay3Reason="JurD-A"
Or Nz(Att.WorkDay3Reason="VacD-A"
)+(Nz(Att.WorkDay4Reason="JurD-A"
Or Nz(Att.WorkDay4Reason="VacD-A"
)+(Nz(Att.WorkDay5Reason="JurD-A"
Or Nz(Att.WorkDay5Reason="VacD-A"
))=5)))I would like to add this condition to the end, but I'm not sure how. This is what I would like:
Then GetNumWeeks()+1 AND RecCnt+0
Also I would need to know how to reset the GetNumWeeks() to the default when the next EmployeeNumber comes up. I have tryed several different things, but I keep getting syntax errors or not a valid SQL command. Thank you in advance for anyone who assists me.
