Non Updateable Query Help

[pdowg881]

I have a sequel query that pulls employee training records. They can be edited and deleted from the query or a related form. When I add a field from third table to the query, even without creating any new joins, the query is not updateable.
The added field just shows records matching the current supervisors department. Any ideas what's causing the issue?
Query Not Updateable

SELECT Employees.ID, Employees.[Last Name], Employees.[First Name], [Employee Training].Course, [Employee Training].[Training Date], [Employee Training].[Next Due], Employees.Department, Employees.[Work Shift], Employees.[Supervisor Name], Employees.[Supervisor Email], [Employee Training].Hours, Employees.[Active?], [Employee Training].Employee, Supervisors.[Supervisor Email Access]

FROM Supervisors, Employees INNER JOIN [Employee Training] ON Employees.ID = [Employee Training].Employee

WHERE ((([Employee Training].[Training Date]) Is Null) AND ((Employees.Department)=[Supervisors].[Department Access]) AND ((Employees.[Active?])=Yes) AND ((Supervisors.[Supervisor Email Access])=GetEmail()))

ORDER BY Employees.[Last Name], Employees.[First Name];


Query Updateable
SELECT Employees.ID, Employees.[Last Name], Employees.[First Name], [Employee Training].Course, [Employee Training].[Training Date], [Employee Training].[Next Due], Employees.Department, Employees.[Work Shift], Employees.[Supervisor Name], Employees.[Supervisor Email], [Employee Training].Hours, Employees.[Active?], [Employee Training].Employee

FROM Employees INNER JOIN [Employee Training] ON Employees.ID = [Employee Training].Employee

WHERE ((([Employee Training].[Training Date]) Is Null) AND ((Employees.[Supervisor Email])=GetEmail()) AND ((Employees.[Active?])=Yes))

ORDER BY Employees.[Last Name], Employees.[First Name];

[pbaldy]

I would suspect that it's because there is no join.

[pdowg881]

I have also tried joining the Employees.Department and Supervisor.[Department Access] fields rather than using Employees.Department = Supervisor.[Department Access]. While yjey both display the same query results, neither results in an updateable query.

[pbaldy]

I assume you're running into one of these:

http://allenbrowne.com/ser-61.html

[pdowg881]

Ran into that list a few times and can't seem to find anything that fits. The query is updateable until I reference any field from the supervisors table, whether it been in the Select statement, Where statement, or through a join.

[CJ_London]

you have a cartesian query (no join). try changing this

((Employees.Department)=[Supervisors].[Department Access])

to an inner join

one thing you can try if you still need multiple tables is to change the recordset type in the form properties to Dynaset Inconsistent updates

a word of warning, updating multiple tables in the same query can have issues if the joins are not maintained, resulting in orphaned records or, even if the query appears updateable, updates are rejected because they don't meet the relationship rules at the time of update (e.g. try to update a child table before the parent table has been updated). The usual process is to have a mainform and subforms, each with a single table recordsource and linked through the linkchild/linkmaster properties of the subform.

[pdowg881]

As I've said, I've tried the query as well by joining the Employees.Department and Supervisor.[Department Access] fields rather than using Employees.Department = Supervisor.[Department Access]. If any part of my query refrences that table in any way it become non-updateable. Would there be a way to pull the data I need from that table into a function, and use that instead as a workaround?

[ItsMe]

There are several reasons why a query with a JOIN will not be updateable. One thing to consider is if the fields that are joined are indexed. You can adjust this property in design view of the tables.