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 qryCurrentEmpDept.dept = qryRestrictedemployeesWithDepts.dept.
Just make sure your subquery returns the departments of employees with infractions, don't try to relate it to qryCurrentEmpDept, 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...however when i try to run the update statement, it says the query isn't updateable. How can I make my query updateable?

@ChipR

[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 VacactionTimeAndDepartmentNumbers SET (...) WHERE departmentNumber NOT IN (subquery)

If you make the VacationTimeAndDepartmentNumbers 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 qryEmpsEarnLeave:

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

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 qryEmpsEarnLeave, 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.

@ChipR

[ChipR]

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

When you say UPDATE qryEmpsEarnLeave 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 VacactionTimeAndDepartmentNumbers SET (...) WHERE departmentNumber NOT IN (subquery)

The target of the UPDATE is VacationTimeAndDepartmentNumbers, 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 VacactionTimeAndDepartmentNumbers SET (...) WHERE departmentNumber NOT IN (subquery)

I am not sure what you are referring to by VacactionTimeAndDepartmentNumbers, specifically the DepartmentNumbers...where did that come from?


@ChipR

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

[ChipR]

Oh, this may happen if fields in your table are not indexed. Any field that you are querying on should be indexed for performance anyway, are yours?

[csolomon]

One more thing worth mentioning. my query that determines which employees are restricted, includes the old department they were in. THis will help me to later determine which employee departments should not receive leave. The rule is that if any employee in a department receives an incident, no employee in that dept receives leave for 2 months. If an employee transfers, he is still under restriction until his old department is off probation.

here is that query:

SELECT (Select top 1 dept from qryDeptTrans where qryDeptTrans.empID=qryRestrictedEmployees.empID and qryDeptTrans.transDt<=qryRestrictedEmployees.incidentDt) AS Dept_At_Time_Of_Incident, qryRestrictedEmployees.empID, qryRestrictedEmployees.empNo, qryRestrictedEmployees.empName, qryRestrictedEmployees.incidentDt, qryRestrictedEmployees.RestrictionEnd, qryCurrentEmpDept.deptID
FROM qryRestrictedEmployees INNER JOIN qryCurrentEmpDept ON qryRestrictedEmployees.empID = qryCurrentEmpDept.empID;

@ChipR

[csolomon]

My fields are indexed

@ChipR

[ChipR]

Well I certainly learned something about why queries are not updatable. I couldn't update my join query until I made at least one side indexed(no duplicates) and the other side indexed also.

So I guess the problem is that the last query you posted still is not updatable even with proper indices?

Unfortunately, it's 5pm here so I won't get to look at it until tomorrow.

[NeoPa]

@ChipR
You may find Reasons for a Query to be Non-Updatable some help.

[FishVal]

Well.

IMHO, the problem requires a bit of strategical thinking.

• If you could get all:
  • fields required for records-to-be-updated recognition
  • fields to be updated
  • values to update fields
  in one updateable query, then filtering part could be well unupdateable and reside in "IN" clause.
• If not, then you may check whether you could reduce joins by getting recognition and/or update values via VBA functions (built-in or your own) in the query.
• If this doesn't help, then pure code solution based on recordsets will work in any case.

Regards,
Fish.

[csolomon]

Hi Chip,

I was reading about why queries aren't updateable and read something that said that you can not have a select clause in your select clause. In my query, (Select top 1 dept from qryDeptTrans where qryDeptTrans.empID=qryRestrictedEmployees.empID and qryDeptTrans.transDt<=qryRestrictedEmployees.incid entDt) AS Dept_At_Time_Of_Incident:

 
SELECT (Select top 1 dept from qryDeptTrans where qryDeptTrans.empID=qryRestrictedEmployees.empID and qryDeptTrans.transDt<=qryRestrictedEmployees.incidentDt) AS Dept_At_Time_Of_Incident, qryRestrictedEmployees.empID, qryRestrictedEmployees.empNo, qryRestrictedEmployees.empName, qryRestrictedEmployees.incidentDt, qryRestrictedEmployees.RestrictionEnd, qryCurrentEmpDept.deptID
FROM qryRestrictedEmployees INNER JOIN qryCurrentEmpDept ON qryRestrictedEmployees.empID = qryCurrentEmpDept.empID;
 
 

This is not allowing my code to be updateable...I need that statement to pull the department at the time of the incident so that I can use it to compare with other employees to determine which ones will or will not receive leave.

@ChipR

[ChipR]

I'm not sure why you're using the Top 1 actually. Why not:
[This is not in code tags because I want to be able to read it without scrolling]

SELECT deptTrans.deptID,
       deptTrans.empID
FROM   deptTrans, qryRestrictedEmployees
WHERE  deptTrans.empID = qryRestrictedEmployees.empID
  AND  qryRestrictedEmployees.incidentDt >= deptTrans.transDt
  AND  (qryRestrictedEmployees.incidentDt <= deptTrans.transEndDt
   OR  deptTrans.transEndDt IS NULL)

That gives you employees on the restricted list with their departments at the time, if I'm right about what the qryRestrictedEmployees does.

[csolomon]

You are right Chip, that works, however it is not updateable either.

[ChipR]

Ok then, what's the qryRestrictedEmployees look like?

[csolomon]

Here is qryRestrictedEmployees:

SELECT Employee.empID, Employee.empNo, Employee.empName, EmpIncidents.incidentDt, DateSerial(Year(EmpIncidents.incidentDt),Month(EmpIncidents.incidentDt)+3,1) AS RestrictionEnd
FROM Employee INNER JOIN EmpIncidents ON Employee.empID=EmpIncidents.empID
WHERE (((DateSerial(Year(EmpIncidents.incidentDt),Month(EmpIncidents.incidentDt)+3,1))>Date()) And ((EmpIncidents.incidentTypeID)=2 Or (EmpIncidents.incidentTypeID)=3));
 

This query is updateable. The difference in the above query and the one that includes the old departments is the select part that then makes the query un-updateable.
@ChipR

[NeoPa]

@ChipR
I appreciate the point Chip, but surely showing the SQL in a structured and clear way is sensible regardless of whether or not wrapping is enabled.

A further point of course, is that there is a simple link provided for all code boxes that switches to wrapping mode.

I don't want to be on your back as we appreciate all our contributors, and you're certainly that.

[NeoPa]

@ChipR

Query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view - No

Your FROM clause precludes this query from being updatable as it currently stands. Did you intend the tables to be unlinked?

[ChipR]

The Wrap link doesn't seem to prevent things from going out of the box, and I don't necessarily want to present this as Code when it's untested or pseudocode.

[ChipR]

I think you're right NeoPa, I wanted an inner join and thought that was equivalent. I wonder if it will be updatable with the inner join on empID syntax?

[csolomon]

Chip,

I definitely understand what you mean by the code and wrapping the text. Often times when I do it I get a weird #&amp...

anyway...even when i did as NeoPa suggested and connected the line, the query still is not updateable:

SELECT deptTrans.deptID, deptTrans.empID
FROM qryRestrictedEmployees INNER JOIN deptTrans ON qryRestrictedEmployees.empID = deptTrans.empID
WHERE (((deptTrans.empID)=[qryRestrictedEmployees].[empID]) AND ((qryRestrictedEmployees.incidentDt)<=[deptTrans].[transEndDt] And (qryRestrictedEmployees.incidentDt)>=[deptTrans].[transDt])) OR (((deptTrans.empID)=[qryRestrictedEmployees].[empID]) AND ((qryRestrictedEmployees.incidentDt)>=[deptTrans].[transDt]) AND ((deptTrans.transEndDt) Is Null));
 

@ChipR

[NeoPa]

May I suggest a possible replacement, that may result in an updatable query (assuming the other elements don't preclude it for any reason) :

SELECT dT.deptID,
       dT.empID
 
FROM   deptTrans AS dT INNER JOIN
       qryRestrictedEmployees AS qRE
  ON   dT.empID=qRE.empID
 
WHERE  qRE.incidentDt>=dT.transDt
  AND (qRE.incidentDt<=dT.transEndDt
   OR  dT.transEndDt IS NULL)

This has an INNER JOIN on the empID, yet still restricts the records selected on the rest of the WHERE clause.

PS. You guys are fast - I was just preparing this and you both popped in :)

[csolomon]

NeoPa,

I tried your query, and it works, but it is not updatable.

[ChipR]

I'm thinking the RestrictionEnd field is calculated, which is preventing the update once the table it's in is joined with the other table. Do you actually need the RestrictionEnd for this?

[FishVal]

Gentlemen,

Check whether you have joined tables either directly or indirectly on M-to-M relationship. If so, no wonder query is not updateable.
IMHO, this is the most probable reason in this case.

[ChipR]

Good point FishVal. Does the qryRestrictedEmployees result in only 1 record for each empID or are there duplicates?

[NeoPa]

@csolomon
You're almost certainly right, but I don't think there is anything in the SQL posted which makes it so.

(assuming the other elements don't preclude it for any reason)

I am trying not to get involved from scratch but simply to help with a specific concept (the said SQL).

Please check the link provided in post #25 for all the reasons which may cause a query to fail to be updatable.

[csolomon]

RestrictionEnd field is a calculated field:

RestrictionEnd: DateSerial(Year(EmpIncidents.incidentDt),Month(EmpIncidents.incidentDt)+3,1)

I do not have to have it there, but I do need to have it at some point. I need to be able to access the incidentDt to calculate it.

Also Chip, when the RestrictionEndDt is apart of the query, it is still updatable.

[csolomon]

Currently, Yes, the restricted employees query only results in 1 record for each empID...If the employee has more than one incident in the alloted time period, there will be more for that employee, but it will be a unique record for each incident.
@ChipR

[Stewart Ross]

In earlier posts in this thread reference has been made to the article on why queries may not be updatable. Reasons are many and varied, and trying to resolve it can be very frustrating.

One way to accomplish updates in these circumstances is to split the task into two sequential components: (1) create a temporary table using a make-table query to set up the rows that will be used to update the source table, then (2) run an update query from the temporary table joined to the table you want updated. As the update is between one table equijoined with another on the same key field or fields the query concerned will always result in updatable rows.

The make-table query is just the select query that you would otherwise have used to update the table you want to change, altered to a make-table type (SELECT INTO statement in SQL) instead...

This is one of the few occasions when an Access Macro is genuinely useful, as you can use a macro to sequence the two queries (the make-table and the update one) to run one after the other using a single named action, an action which is not hidden behind VBA code. Alternatively, you can use a VBA command button to accomplish the same task by running two action queries in sequence.

Either way, you may find it more profitable to try two separate actions than sorting out why your source query is non-updatable.

-Stewart

[ChipR]

In that case, the JOIN on qryRestrictedEmployees and deptTrans is creating a many to many relationship on empID. It might be possible to get all this information in an updatable query, but I don't see how at the moment. Personally, I would just write some code that stepped through recordsets and did the update one row at a time with the click of a button and be done with it.

[FishVal]

Well.

At the very start of the thread Chipr made a valuable suggestion as for using IN clause.
There is no need to filter recordset with joins before update as soon as those joins make it unupdateable.
Get [empID]'s of records to be updated with a separate query, no matter whether it is updateable or not and use this query as criteria in IN clause to filter dataset which contains only fields to be updated with values to update fields with (something tells me it will be updateable).

[ChipR]

I think FishVal is saying you can

UPDATE VacationTime
SET (...)
WHERE EmpID
IN (possibly not updatable subquery)

So simple!

[csolomon]

OK Guys,

Here is what I have:
UPDATE qryEmpsEarnLeave INNER JOIN VacationTime ON qryEmpsEarnLeave.empID = VacationTime.vacaID SET VacationTime.empID = [vacationTime].[empId], VacationTime.empEarnedTime = [empEarnedTime]+4, VacationTime.vacaTimeAwardDt = Date()
WHERE (((qryEmpsEarnLeave.deptID) Not In (SELECT dT.deptID FROM deptTrans AS dT INNER JOIN qryRestrictedEmployees AS qRE ON dT.empID=qRE.empID WHERE qRE.incidentDt>=dT.transDt AND (qRE.incidentDt<=dT.transEndDt OR dT.transEndDt IS NULL))));

I noticed that it updated in the query, but the wrong employees were updated.

@ChipR

[csolomon]

Hey Guys,

I figured out why it wasn't working. I had the empID and deptIDs joined as opposed to the empIDs.

Here is the final query:

UPDATE VacationTime INNER JOIN qryEmpsEarnLeave ON VacationTime.empID=qryEmpsEarnLeave.empID SET VacationTime.empID = [vacationTime].[empId], VacationTime.empEarnedTime = [empEarnedTime]+4, VacationTime.vacaTimeAwardDt = Date()
WHERE (((qryEmpsEarnLeave.deptID) Not In (SELECT dT.deptID  FROM   deptTrans AS dT INNER JOIN        qryRestrictedEmployees AS qRE   ON   dT.empID=qRE.empID  WHERE  qRE.incidentDt>=dT.transDt   AND (qRE.incidentDt<=dT.transEndDt    OR  dT.transEndDt IS NULL))));
 

I still have to do some tests to determine if it still works in all cases, but I appreciate all of the input to get me to this point.



@csolomon