Joining Queries

[Yam84]

Hello:

I am trying to put together a list that is a combination of two queries however I am not sure how to go about it. All attempts to join them have been futile

My first query determines which departments have had an incident within the past two months.

 SELECT EmpIncidents.incidentID, EmpIncidents.empID, EmpIncidents.incidentTypeID, EmpIncidents.incidentDt, Dept.dept, IIf([IncidentTypeID]=1,"No","Yes") AS Restricted, IIf([IncidentTypeID]=1,Null,DateAdd("m",2,[IncidentDt])) AS EndDt, EmpIncidents.deptID AS OLDdeptID
FROM EmpIncidents INNER JOIN Dept ON EmpIncidents.deptID = Dept.deptID
WHERE (((EmpIncidents.incidentTypeID) In (2,3)) AND ((([EmpIncidents].[incidentDt]) Between DateSerial(Year(Date()),Month(Date())-2,1) And Date())));

My second query is determines what department each employee is in currently

  SELECT Employee.empID, DeptTrans.deptID AS CurrentDept, Employee.empName
FROM Employee, DeptTrans
WHERE (((Employee.empID)=[DeptTrans].[empID]) AND ((DeptTrans.transEndDT) Is Null));

I need to filter out employees who are in a department that has had an incident, regardless of whether they are the employee in that department who received the incident. I tried joining them on deptID, but that did not produce a correct list. I plan to use this list to update employee leave. Am I missing something??

Any assistance would be greatly appreciated.

 

[Remou]

Does this give the list you need?

SELECT DISTINCT Employee.empID, Employee.empName
FROM DeptTrans 
INNER JOIN Employee 
ON DeptTrans.empID = Employee.empID
WHERE DeptTrans.deptID In (
      SELECT deptID
      FROM EmpIncidents
      WHERE incidentTypeID In (2,3))
OR Employee.empID In (
     SELECT EmpID 
     FROM EmpIncidents 
     WHERE incidentTypeID In (2,3)) 
OR Employee.empID In (
     SELECT d.empID
     FROM EmpIncidents AS e 
     INNER JOIN deptTrans AS d 
     ON e.deptID = d.deptID
     WHERE e.incidentTypeID In (2,3)
     AND e.incidentDt Between [transDt] And Nz([transEndDt],Date()))

http://lessthandot.com

 

[PHV]

What about this ?

SELECT DISTINCT E.empID, E.empName
FROM (EmpIncidents AS I
INNER JOIN DeptTrans AS T ON I.deptID = T.deptID)
INNER JOIN Employee AS E ON T.empID = E.empID
WHERE I.incidentTypeID In (2,3) AND I.incidentDt >= DateSerial(Year(Date()),Month(Date())-2,1)
AND I.incidentDt >= T.transDt AND (I.incidentDt <= T.transEndDt OR T.transEndDt Is Null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Yam84]

PHV,

SELECT DISTINCT E.empID
FROM Employee AS E INNER JOIN (EmpIncidents AS I INNER JOIN DeptTrans AS T ON I.deptID = T.deptID) ON E.empID = T.empID
WHERE (((I.incidentDt)<=[T].[transEndDt] And (I.incidentDt)>=DateSerial(Year(Date()),Month(Date())-2,1) And (I.incidentDt)>=[T].[transDt]) AND ((I.incidentTypeID) In (2,3))) OR (((I.incidentDt)>=DateSerial(Year(Date()),Month(Date())-2,1) And (I.incidentDt)>=[T].[transDt]) AND ((I.incidentTypeID) In (2,3)) AND ((T.transEndDt) Is Null)))

This query works in that it provides the list of employees who should not receive leave based on the incidents they had. I was wondering if you could let me know if I could also make the query look to see if an employee has transferred to a new department AFTER that department has been placed on restriction, how can I ensure that the new employee will NOT be put on restriction?

For instance, Sallie has an incident on 1.1.09 in the Service department. John transfers to the Service Department on 1.2.09, therefore since John transferred after the incident was received, he should not be penalized.

Is it possible that I could just do the inverse of the query to get that criteria working in an OR clause?