Assistance with a Query

[csolomon]

Here is qryRestrictedEm ployees:

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.

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

[NeoPa]

[This is not in code tags because I want to be able to read it without scrolling]

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]

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)

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

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.

[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 qryRestrictedEm ployees result in only 1 record for each empID or are there duplicates?

[NeoPa]

NeoPa,

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

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 RestrictionEndD t 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.

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

[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