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]

How about:

SELECT Employee.empID, Employee.empName
FROM (Employee 
INNER JOIN (
    SELECT deptID 
    FROM DeptTrans d
    WHERE d.transEndDT Is Null) AS Dept
ON Employee.empID=Dept.empID)
LEFT JOIN (
    SELECT e.deptID
    FROM EmpIncidents e
    WHERE e.incidentTypeID In (2,3)
    AND e.[incidentDt] 
       Between DateSerial(Year(Date()),Month(Date())-2,1)
       And Date()) AS Incident
ON Incident.deptID = Dept.deptID
WHERE Incident.deptID Is Null

http://lessthandot.com

 

[Yam84]

Hi Remou,

I appreciate your response. I was not able to get this query to produce any results, as I am receiving an error: Syntax error in Join operation. It highlights the DeptTrans in this line:

FROM (Employee INNER JOIN 
(SELECT deptID FROM DeptTrans d

 

[Remou]

I am not sure why you got the error you did, however, I see I have made a mistake:

SELECT Employee.empID, Employee.empName
FROM (Employee
INNER JOIN (
    SELECT d.deptID, d.empID
    FROM DeptTrans d
    WHERE d.transEndDT Is Null) AS Dept
ON Employee.empID=Dept.empID)
LEFT JOIN (
    SELECT e.deptID
    FROM EmpIncidents e
    WHERE e.incidentTypeID In (2,3)
    AND e.[incidentDt]
       Between DateSerial(Year(Date()),Month(Date())-2,1)
       And Date()) AS Incident
ON Incident.deptID = Dept.deptID
WHERE Incident.deptID Is Null

http://lessthandot.com

 

[Yam84]

Remou,

Thanks for the repost. While I am able to get a result, it's not the correct results. it includes a record for an employee who transferred to another department from a department that had an incident, which means this employee should NOT be in this list.

 

[HarleyQuinn]

Is this the part of your query that detmines if the employee is currently in that dept?

WHERE d.transEndDT Is Null

?

If so, just remove that from the first query in Remou's post.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[Yam84]

Hello:

I removed that part and I am not getting the right employees.

 

[Remou]

Ok.

SELECT Employee.empID, Employee.empName
FROM (Employee
INNER JOIN (
    SELECT d.deptID, d.empID
    FROM DeptTrans d
    WHERE d.transEndDT Is Null) AS Dept
ON Employee.empID=Dept.empID)
LEFT JOIN (
    SELECT d.deptID
    FROM EmpIncidents e 
    INNER JOIN DeptTrans d
    ON e.empID=d.empID
    WHERE e.incidentTypeID In (2,3)
    AND e.[incidentDt]
       Between DateSerial(Year(Date()),Month(Date())-2,1)
       And Date()) AS Incident
ON Incident.deptID = Dept.deptID
WHERE Incident.deptID Is Null

http://lessthandot.com

 

[Yam84]

Remou,

Thank you for your p ersistance, however this list is not correct either...What information can i provide to assist? I expect this list to include employees who have had incidents as well as all other employees in the departments.

 

[Remou]

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."

You ,mean you need to include employees, not "filter out" yesno?

http://lessthandot.com

 

[Remou]

If so:

SELECT Employee.empID, Employee.empName
FROM (Employee
INNER JOIN (
    SELECT d.deptID, d.empID
    FROM DeptTrans d
    WHERE d.transEndDT Is Null) AS Dept
ON Employee.empID=Dept.empID)
INNER JOIN (
    SELECT d.deptID
    FROM EmpIncidents e
    INNER JOIN DeptTrans d
    ON e.empID=d.empID
    WHERE e.incidentTypeID In (2,3)
    AND e.[incidentDt]
       Between DateSerial(Year(Date()),Month(Date())-2,1)
       And Date()) AS Incident
ON Incident.deptID = Dept.deptID[/code

[URL unfurl="true"]http://lessthandot.com[/URL]

 

[Yam84]

Remou,

I apologize, I did mean that I needed to include all employees in the matching department that had an incident.

The results of your query returns ALL employess (and some duplicates); those that have an incident and those that do not, regardless of the dept.

 

[Remou]

You want all current employees of each dept where an incident has occurred, regardless of whether or not that employee was in the dept at the time the incident occurred.
You do not want employees who have moved from the dept where the incident occurred.

[tt]Tables
Employees
---------
Employee ID

DeptTrans
---------
Employee ID
Current Department, defined by transEndDT is null.

Incident Table
--------------
Dept at time of incident
Employee ID[/tt]

Is the above correct?

http://lessthandot.com

 

[Yam84]

I want all employees in a department, where a member of that department has had an incident. (If an employee has an incident, that means every employee in that dept is on restriction from receiving leave until the first day of the 3rd month. This list will be used to construct a list of emps who will receive leave. This list should include any employees who have transferred from a dept that is on restriction for having an incident. Although in a new department, they are still under restriction with their old department.

Here is my table structure:
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
A Null value in the transEndDt indicates the current department of the
employee
(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, deptID, incidentTypeID, incidentDt
1, 1, 1, 2, 12.2.08
2, 2, 3, 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

 

[Remou]

Does this produce the list you require?

SELECT DeptTrans.deptTransID, Employee.empID, Employee.empName
FROM DeptTrans INNER JOIN Employee ON DeptTrans.empID = Employee.empID
WHERE DeptTrans.deptTransID In 
   (Select DeptID 
    From EmpIncidents 
    Where incidentID In (2,3))
OR Employee.empID In 
    (Select EmpID 
     From EmpIncidents 
     Where incidentID In (2,3))

http://lessthandot.com

 

[Yam84]

No, it doesn't.

 

[Remou]

How about:

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))

The result is:

[tt]
empID empName
1 Thomas Johnson 'Involved in incident 1
2 Tom Mantia 'Involved in incident 2
3 JohnTest 'Employed in dept 3, ie incident 2[/tt]

Do you also need to compare the date of the incident to the date the emplyee was in the department to see if the employee was there at the time of the incident?

http://lessthandot.com

 

[Yam84]

I need to check to see if the employee has transferred from a department with in the last two months to determine if there are any employees in departments that do not have incidents, that transferred from another department, which did have an incident. This means that although they are on a new team, they should still not receive leave along with their old departments.

Not only are the employees who committed the incident punished, but everyone in their department and everyone who was in their department at the time of the incident.

 

[Remou]

Have you considered decimation?

http://lessthandot.com

 

[Yam84]

Yes I have; many time throughout this project