Help with a select query with a twist

[quest4]

I would like to thanks, in advance, anyone who can help me with this problem. To start with, I am tring to query a table, with hours work per day and reasons per day, there are 5 columns each for each week the employee work. I am querying the reasons field looking for text code matches. I am trying to get a list of employees who have 13 weeks of perfect attendence. Holidays, HolD-A, will count as regular days, if marked. I have one query, qryEmployeeAttendenceBonsuses1, which does exactly that and it works great. Sounds easy so right? Here is the catcher and this is where it gets really tough. Vacations, VacD-A and Jury Duty, JurD-A, which are usually in weeks, and I have a query,qryVacJuryRecsPerEmployee1, which does get a list of the employees and the week(s) which they are off. Those week(s) we don't want to be counted, we want to treat them as if they don't even exist. For exampple, normally we would examine weks 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 amd 13, if however, there was a vacation week in week 10, we would then look at weeks 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, and 14. As you can see, I am counting 13 weeks, but less the vacation week. Also, as we all know vacation day(s) may not be a full week, but if they are three or more days, we want that week treated the same as a vacation week. If, however there is only one or two days used for vacation or juryduty then they can be treated like the holiday and counted as a regular day. I have a query, qryVacJuryRecsPerEmployee2,which does collect the employee number and the week starting dates for all of these types of occuences. So, with these three queries up and working, my main problem is how to get them to work to gether to generate the list of employees with perfect attendence for 13 weeks? And to exempt the parameters? Thanks you in advance for any help.

 

[MichaelRed]

This is all way to much for my poor tired grey stuff. I would probably just try to create a recordset which lists the 13 weeks for each employee. For the start of an 'exercise', clear the table. Add all employees who 'participate' (willingly or NOT!) in this macabre scheme. For each employee for each week, update the fiels to designalte the weeks 'attendance' (probably an update query based on the various bits and pieces you discuss in the oriningal post.

Each weeks 'attendance' could be an integer, with the days added from each of your queries, so that -for instance- if the employee were on vacation for three+ days, that query would add 5 days to the attendance, if they also worked two days, that would add the twh (and YES, they might end up with 7 days of attendance). When all of hte accountable days have been added, just do another query for those who have at least five days of attendance for every week.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[quest4]

Thank, for the response. Maybe I failed to mention that I am using Access 2000, and that I have got the tables populated with just a few dumbie employees, and the attendence table is populated with 15 weeks of attendence data for them. Therefore, I can monipulated the data any way that is needed to test results. If and when I can get this beastly little query working correctly, then we will load it with live data and real employees. I am doing a record count in the first query where it is check for Null, or no missed time, and for holidays. That part is working fine, it's the vacations/juryduty that is what is giving me a fit. I got that info in two other queries, but I don't know what to do with it or more importantly how. Thanks you again.

 

[quest4]

Let try something different, a two part attack. First lets try getting the 1 and 2 days of vacation or jury duty to be counted like holidays. This is the query:
SELECT Att.EmployeeNumber,
Emp.NameFirst,
Emp.NameInitial,
Emp.NameLast,
COUNT(Att.EmployeeNumber) AS RecCnt
FROM tblEmployees AS Emp LEFT JOIN tblAttendence AS Att
ON Emp.EmployeeNumber=Att.EmployeeNumber
WHERE (Emp.EmpStatusType="Active"
And Emp.PayType="per Hour&quot
And (Att.DateWeekStarting
Between Nz([Enter Start Date],Date()-7)
And Nz([Enter End Date],DateAdd("ww",-14,Date())))
And ((Att.WorkDay1Reason Is Null Or Att.WorkDay1Reason="HolD-A&quot
And (Att.WorkDay2Reason Is Null
Or Att.WorkDay2Reason="HolD-A&quot
And (Att.WorkDay3Reason Is Null
Or Att.WorkDay3Reason="HolD-A&quot
And (Att.WorkDay4Reason Is Null
Or Att.WorkDay4Reason="HolD-A&quot
And (Att.WorkDay5Reason Is Null
Or Att.WorkDay5Reason="HolD-A&quot)
And ((Abs((Nz(Att.WorkDay1Reason="JurD-A&quot
Or Nz(Att.WorkDay1Reason="VacD-A&quot)
+(Nz(Att.WorkDay2Reason="JurD-A&quot
Or Nz(Att.WorkDay2Reason="VacD-A&quot)
+(Nz(Att.WorkDay3Reason="JurD-A&quot
Or Nz(Att.WorkDay3Reason="VacD-A&quot)
+(Nz(Att.WorkDay4Reason="JurD-A&quot
Or Nz(Att.WorkDay4Reason="VacD-A&quot)
+(Nz(Att.WorkDay5Reason="JurD-A&quot
Or Nz(Att.WorkDay5Reason="VacD-A&quot))=1)
Or (Abs((Nz(Att.WorkDay1Reason="JurD-A&quot
Or Nz(Att.WorkDay1Reason="VacD-A&quot)
+(Nz(Att.WorkDay2Reason="JurD-A&quot
Or Nz(Att.WorkDay2Reason="VacD-A&quot)
+(Nz(Att.WorkDay3Reason="JurD-A&quot
Or Nz(Att.WorkDay3Reason="VacD-A&quot)
+(Nz(Att.WorkDay4Reason="JurD-A&quot
Or Nz(Att.WorkDay4Reason="VacD-A&quot)
+(Nz(Att.WorkDay5Reason="JurD-A&quot
Or Nz(Att.WorkDay5Reason="VacD-A&quot))=2))
GROUP BY Att.EmployeeNumber, Emp.NameFirst, Emp.NameInitial, Emp.NameLast
HAVING COUNT(Att.EmployeeNumber)=13
ORDER BY Att.EmployeeNumber;
Now in the top half it is correctly working and counting. In the bottom half, the Abs section it is correctly identifing the 1 and 2 vacation and jury duty day(s), but the ten dollar question is how do I get the vacation/jury duty day(s) to become part of the count like the hjolidays are? Thanks you for any help.

 

[quest4]

I have figured out how to get the 1 and 2 day(s) Vacation / jury days figured into the count. The only thing left is to get the week vacations or jury duty out of the count. Dooes any one have any ideas on this one? Any bracve souls out there, who like a challenge? Thanks in advance to any one who helps.