Assistance with a Query

[csolomon]

Hello:

I am trying to put together a query that determines employees who are in a department that has had an incident with in the last two months. I have a query that determines who has had and incident and the department they were in when they had the incident:

SELECT Q_RestrictedEmployees.empID, Q_RestrictedEmployees.empNo, Q_RestrictedEmployees.empName, Q_RestrictedEmployees.incidentDt, Q_RestrictedEmployees.RestrictionEnd, (Select top 1 dept from Q_DeptTrans where Q_DeptTrans.empID=Q_RestrictedEmployees.empID and Q_DeptTrans.depttransDt<=Q_RestrictedEmployees.incidentDt) AS Dept_At_Time_Of_Incident
FROM Q_CurrentDepts INNER JOIN Q_RestrictedEmployees ON Q_CurrentDepts.empID = Q_RestrictedEmployees.empID;
 

I believe i need to use this query in conjunction with another query to match anyone in a department from above to all other employees in that department, so I may penalize that department as a whole. I have a query that tells me all employed currently and their current department, but I used that one in the above query to get my record set. How can I find other employees with in the department of the offending employees?

[ChipR]

Just make a JOIN or query that includes it, that's what you need.

[csolomon]

There in lies the problem. I have a query that has the vacationTime as well as the current department of the employee, but any query that I use the qryCurrentEmpDe pt in will not be updateable because that query isn't updateable. If i take that query away, i can update it, but then i would only have access to the vacatime TIme, and not the departmentID.


@ChipR

[ChipR]

I see what you are saying now. That first query you listed is qryCurrentEmpDe pt? I guess tell me your table structures. It can't be that difficult to get the employee's department in an updatable query.

[csolomon]

Here is my table structure and some sample data:

Employee: empID, empNo, empName

1, 12345, Thomas Johnson
2, 67890, Tom Mantia
3, 54321, JohnTest
 

Dept: deptID, dept

1, Engineering
2, Yard
3, Sandblast

DeptTrans: deptTransID, deptID, empID, transDt, transEndDt

1, 1, 1, 1.1.09, Null
2, 2, 1, 12.1.08, 12.31.08
3, 3, 2, 1.2.09, Null
4, 3, 3, 1.3.09, Null

(One employee may have transferred departments many times)

(One employee may be assigned vacation time many times)

VacationTime: vacaID, empID, empEarnedTime, vacaTimeAwardDt

1, 1, 8, 2.1.09
2, 2, 0, Null
3, 3, 4, 2.1.09

(One employee maybe have many incidents)

EmpIncidents: incidentID, empID, incidentTYpeID, incidentDt

1, 1, 2, 12.2.08
2, 2, 2, 1.4.09

(One employee can have many types of incidents)

IncidentTypeId: incidentTypeID, incident (the only incidents that
allow an employee and his dept to lose time are types 2 and 3)

1, NonRecordable
2, Recordable
3, Lost-Time 

@ChipR

[ChipR]

I don't get it. What department is an employee in if they never transferred?

[csolomon]

If they never transferred their transEndDt is Null, meaning they are still in their current department


DeptTrans: deptTransID, deptID, empID, transDt, transEndDt

 
   1. 1, 1, 1, 1.1.09, Null
   2. 2, 2, 1, 12.1.08, 12.31.08
   3. 3, 3, 2, 1.2.09, Null
   4. 4, 3, 3, 1.3.09, Null

Specifically in the above example, empID 1 has transferred departments twice. His current dept is deptID 1 and his end date is Null. He was in deptID 2 all of 2008 and on 1.1.09, he transferred to dept 1 and is still apart of that dept.

[ChipR]

Ok, assuming you put in a transfer when they start the job.

How about:

SELECT Employee.empID, Employee.empNo, Employee.empName, DeptTrans.deptID FROM Employee, DeptTrans WHERE Employee.empID = DeptTrans.empID AND transEndDT IS NULL

[csolomon]

Correction, I made it updateable :)

---

Correct, when an employee starts a job, they are put in the transfer table. That query works, but it isn't updateable either.

@ChipR

[ChipR]

That simple query by itself cannot be updated? I just tried a query with IS NULL and didn't have a problem. I don't see why that would be.

[ChipR]

Never mind, it's not updatable after the join. What the heck.