Assistance with a Query

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

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

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

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.

[csolomon]

My fields are indexed

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?

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

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.

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.em pID=qryRestrict edEmployees.emp ID and qryDeptTrans.tr ansDt<=qryRestr ictedEmployees. incidentDt) AS Dept_At_Time_Of _Incident:

SELECT [B](Select top 1 dept from qryDeptTrans where qryDeptTrans.empID=qryRestrictedEmployees.empID and qryDeptTrans.transDt<=qryRestrictedEmployees.incidentDt)[/B] 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.

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.

[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 qryRestrictedEm ployees does.

[csolomon]

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

[ChipR]

Ok then, what's the qryRestrictedEm ployees look like?