Update table 1 from TOP value in table 2 - Error

[journeyman]

Hi All,



I am attempting to update a main items table with the last record results from another table.

In the follow example, the SELECT statement provides the result - i.e. the last item in the Details table.

UPDATE Tbl_WorkflowItems
SET CurrentTask = (
    SELECT TOP 1 d.TaskName
    FROM Tbl_WorkflowDetails d
    WHERE d.WorkFlowID=27 AND d.Completed=True
    ORDER BY d.ItemNumber DESC)
WHERE ID = 27

However, the error I'm receiving is Operation must use an updateable query.

The query is very clearly an update query. Both tables can be updated. So what gives?

Thanks in advance for any good help

Cheers

[June7]

Yes, it is an UPDATE query but it is not an updateable query, according to Access.

Saving this sort of calculated data is usually a bad idea. If it can be calculated for UPDATE, it can be calculated when needed.

If you really must save, options are:

1. save the TOP 1 data to a temp table and use that table as source

2. VBA looping recordset

3. domain aggregate functions

[Micron]

Both tables can be updated.

That fact means nothing with respect to the query. What you have is a query with a sub query. Those have never been updatable as far as I can recall. Maybe this would be of interest to you
http://allenbrowne.com/ser-61.html