Operation must use an updateable query?????

[UHNSTrust]

I have the following select query which I would then like to use to update a table with a value. I keep getting 'Operation must use an updateable query' error message.

SELECT CouncilData.EntSys, CouncilData.ASS_CODE, CouncilData.ASS_DATE_TIME, Min(CLIENT_ASS_TAB_DETAILS.TAB_DATE) AS [Date Service Started]
FROM CLIENT_ASS_TAB_DETAILS INNER JOIN CouncilData ON (CLIENT_ASS_TAB_DETAILS.EntSys = CouncilData.EntSys) AND (CLIENT_ASS_TAB_DETAILS.ASS_CODE = CouncilData.ASS_CODE) AND (CLIENT_ASS_TAB_DETAILS.ASS_DATE_TIME = CouncilData.ASS_DATE_TIME)
WHERE (((CLIENT_ASS_TAB_DETAILS.TAB_NO) Between "24" And "39"))
GROUP BY CouncilData.EntSys, CouncilData.ASS_CODE, CouncilData.ASS_DATE_TIME
HAVING (((Min(CLIENT_ASS_TAB_DETAILS.TAB_DATE)) Is Not Null))

I want to then update the field [Date Service Started] in table 'CouncilData' with the field [Date Service Started] in the above query.

I have tried creating an update query based on the above select query but get the error message. I know I could do a maketable query from the above select query and then use the table in the update query but this does seem very cumbersome.

Can anybody help with the SQL that can get my table updated?

Hope this makes sense.

Thanks in advance
Jonathan

 

[fneily]

See:
Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

Especially the statement - Queries with aggregated data are not updatable