Why is this query not updateable?

[nschroeder]

I have an Employees table and a couple of queries, qryEmpNames and qryEmployees. qryEmpNames concatenates the first and last names into a Name field, and qryEmployees links the Employees table with qryEmpNames, linked by employee ID. Why would the data in qryEmployees not be updateable? I've read the Allen Browne article on Why is My Query Read-Only, but I don't see that any of those issues apply here. What am I missing?

Here's the SQL code from qryEmpNames:

SELECT Employees.EmpNum, Employees.EmployeeID, [FirstName] & " " & [LastName] AS Name
FROM Employees;

And here's qryEmployees:

SELECT [qryEmpNames].Name, Employees.*
FROM Employees LEFT JOIN [qryEmpNames] ON Employees.EmpNum = [qryEmpNames].EmpNum;

Thanks in advance.

 

[MarkK]

In your final query you expose a field that in the base query is the concatenation of two fields. This is not editable in the final query because there is no mechanism to un-concatenate edits to that field.

 

[nschroeder]

I can understand that I wouldn't be able to update the name, but the rest of the fields are locked as well. Is it always all-or-nothing with queries?

 

[nschroeder]

Through trial-and-error, and attempts to eliminate suspects, I've concluded that there is some other problem here. I reduced the queries to the following and it still won't allow updates. I also created new queries from scratch and compacted & repaired the db with no better results. It still won't let me update from qryEmployees. It must be something obvious, so someone please help me with my blind spot!

qryEmpNames:

SELECT Employees.EmpNum
FROM Employees;

qryEmployees:

SELECT Employees.*
FROM Employees LEFT JOIN [qryEmpNames] ON Employees.EmpNum = [qryEmpNames].EmpNum;

 

[boblarson]

do you have a primary key set on the table?

 

[nschroeder]

Yes, EmpNum is pk.

 

[boblarson]

Well, I would say your problem is that you are doing a join to the original table with a query that is using the same table.

 

[nschroeder]

Yea, apparently that can't be done. I'll create a function to concatenate the names instead of using a query.
Thanks.