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]

Try the IN or NOT IN.

Then you can select all employees whose department is NOT IN the list of departements of employees who have had an incident.

SELECT *
FROM Employees
WHERE Department NOT IN (SELECT Department FROM ...you've already got this part...)

Hope that helps.

Chip

[csolomon]

Hi Chip,

I appreciate your response. I am having trouble with this query. My query does not return anything. Here it is:

SELECT qryCurrentEmpDept.empID, qryCurrentEmpDept.dept
FROM qryCurrentEmpDept
WHERE (((qryCurrentEmpDept.dept) Not In (select distinct Dept_At_Time_Of_Incident FROM qryRestrictedEmployeesWithDepts WHERE qryCurrentEmpDept.dept = qryRestrictedemployeesWithDepts.dept)));

The first part selects the empID and dept of the employees in their current department. The subquery is a list of departments with infractions. I told it (or I thought I told it) to give me all employees and departments not in that list.
My query returns nothing.

[ChipR]

You don't need the qryCurrentEmpDe pt.dept = qryRestrictedem ployeesWithDept s.dept.
Just make sure your subquery returns the departments of employees with infractions, don't try to relate it to qryCurrentEmpDe pt, that's what the NOT IN does.

[NeoPa]

As an alternative, you can link to the query, as a named query or even a subquery (Subqueries in SQL), with an INNER JOIN.
EG.

SELECT A,
       [Group]
FROM   MainTable INNER JOIN qryGroups
  ON   MainTable.Group=qryGroups.Group

[csolomon]

I was able to get the query to return what I needed by taking out the

qryCurrentEmpDept.dept = qryRestrictedemployeesWithDepts.dept

However since this query is not updateable, I have not been able to accomplish my goal (which ultimately is an update statement, which adds employee leave).

Here is my first query:

SELECT qryCurrentEmpDept.empID, qryCurrentEmpDept.dept
FROM qryCurrentEmpDept
WHERE (((qryCurrentEmpDept.dept) Not In (select distinct Dept_At_Time_Of_Incident FROM qryRestrictedEmployeesWithDepts)));

This query returns departments who are eligible to receive leave.

The next query joins the query above with the vacationTime table to assign leave:

UPDATE qryEmpsEarnLeave INNER JOIN VacationTime ON qryEmpsEarnLeave.empID = VacationTime.vacaID SET VacationTime.vacaID = [vacaID], VacationTime.empID = [vacationTime].[empId], VacationTime.empEarnedTime = [empEarnedTime]+4, VacationTime.vacaTimeAwardDt = Date();

When the query is a select query, it shows me the two employees who belong to the department with no incident, whose leave should be updated...howev er when i try to run the update statement, it says the query isn't updateable. How can I make my query updateable?

You don't need the qryCurrentEmpDe pt.dept = qryRestrictedem ployeesWithDept s.dept.
Just make sure your subquery returns the departments of employees with infractions, don't try to relate it to qryCurrentEmpDe pt, that's what the NOT IN does.

[ChipR]

You may not be able do UPDATE on a query containing NOT IN, but you don't have to. The NOT IN should just be in the WHERE clause.
You want to:
UPDATE VacactionTimeAn dDepartmentNumb ers SET (...) WHERE departmentNumbe r NOT IN (subquery)

If you make the VacationTimeAnd DepartmentNumbe rs query based on 2 simple tables, it will be updatable.

[csolomon]

Chip,

I am trying to follow your advice, but i am not getting anywhere really.

Let me start from the beginning: I have queries that I use in other queries...the one that isn't updateable is the one that queries the current department of the employee. (I keep a transfer history)

SELECT DeptTrans.empID, DeptTrans.transDt, Dept.dept
FROM Dept, qryMostRecentDeptTransDate, DeptTrans
WHERE qryMostRecentDeptTransDate.MaxOftransDt = DeptTrans.transDt
AND qryMostRecentDeptTransDate.empID = DeptTrans.empID
AND Dept.deptID = DeptTrans.deptID;

I use this query in a query called qryEmpsEarnLeav e:

SELECT qryCurrentEmpDept.empID, qryCurrentEmpDept.dept
FROM qryCurrentEmpDept
WHERE (((qryCurrentEmpDept.dept) Not In [B](select distinct Dept_At_Time_Of_Incident FROM qryRestrictedEmployeesWithDepts)[/B]));

The subquery highlighted in bold returns departments that have incidents. So the query should ultimately return employees in departments who do not have incidents. This isn't updateable either.

I have a query that determines how much leave each employee has (updateable):

SELECT VacationTime.vacaID, VacationTime.empID, VacationTime.empEarnedTime, VacationTime.vacaTimeAwardDt
FROM VacationTime;

That combined with the qryEmpsEarnLeav e, produces what I wanted to be my update query:

UPDATE qryEmpsEarnLeave INNER JOIN qryVacaTime ON qryEmpsEarnLeave.empID = qryVacaTime.empID SET qryVacaTime.vacaID = [vacaID], qryVacaTime.empID = [qryVacaTime].[empId], qryVacaTime.empEarnedTime = [empEarnedTime]+4, qryVacaTime.vacaTimeAwardDt = [vacaTImeAwardDt];

Unfortunately, this is still not updateable. In your response you said :

The NOT IN should just be in the WHERE clause.

I believe all of mine are in the Where clause.

You may not be able do UPDATE on a query containing NOT IN, but you don't have to. The NOT IN should just be in the WHERE clause.
You want to:
UPDATE VacactionTimeAn dDepartmentNumb ers SET (...) WHERE departmentNumbe r NOT IN (subquery)

If you make the VacationTimeAnd DepartmentNumbe rs query based on 2 simple tables, it will be updatable.

[ChipR]

Can you change you qryVacaTime so that it also has the employee's departmentID?

When you say UPDATE qryEmpsEarnLeav e JOIN...
you are trying to update on a query that contains a NOT IN, which you can't do.

Try it like I said in the last post,
UPDATE VacactionTimeAn dDepartmentNumb ers SET (...) WHERE departmentNumbe r NOT IN (subquery)

The target of the UPDATE is VacationTimeAnd DepartmentNumbe rs, which is updatable because it will not have the NOT IN in it.

Currently your NOT IN is in a WHERE clause, but you are then trying to UPDATE on the result of it, which you can't do.

Sorry, I know this is coming out really confusing but I can't think of any better way to explain it.

[csolomon]

Chip,

You say to :

Try it like I said in the last post,
UPDATE VacactionTimeAn dDepartmentNumb ers SET (...) WHERE departmentNumbe r NOT IN (subquery)

I am not sure what you are referring to by VacactionTimeAn dDepartmentNumb ers, specifically the DepartmentNumbe rs...where did that come from?

Can you change you qryVacaTime so that it also has the employee's departmentID?

When you say UPDATE qryEmpsEarnLeav e JOIN...
you are trying to update on a query that contains a NOT IN, which you can't do.

Try it like I said in the last post,
UPDATE VacactionTimeAn dDepartmentNumb ers SET (...) WHERE departmentNumbe r NOT IN (subquery)

The target of the UPDATE is VacationTimeAnd DepartmentNumbe rs, which is updatable because it will not have the NOT IN in it.

Currently your NOT IN is in a WHERE clause, but you are then trying to UPDATE on the result of it, which you can't do.

Sorry, I know this is coming out really confusing but I can't think of any better way to explain it.

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

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

[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

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.

[ChipR]

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