Hi,
I have an update query which is not updateable for reasons I cannot fathom.
I have two tables, tbl_Response and tbl_Activity. BOTH have a CaseID field which is indexed Duplicates OK (each case can have several responses and several activity records). I want to update a date field in the Activity table with the date of the latest response for each case.
So I have a select query qry_LatestResponse on the Responses table which GROUPS BY CaseID, with Max(ResponseDate). So CaseID is unique in this query.
In my update query I join my Activity table with qry_LatestResponse on CaseID. All the fields being updated are in the Activity table (i.e. on the many side of the join).
Here is the SQL from my 2 queries...
qry_LatestResponse:
qry_SELECT tbl_CC_Response.TCC_ID, Max(tbl_CC_Response.fResponseDate) AS LastResponseDate
FROM tbl_CC_Response
WHERE (((tbl_CC_Response.ResponseType)="CC01" Or (tbl_CC_Response.ResponseType)="CC20") AND ((tbl_CC_Response.Processed) Is Null))
GROUP BY tbl_CC_Response.TCC_ID;
My update query:
UPDATE tbl_Activity INNER JOIN qry_LatestResponse ON tbl_Activity.TCC_ID = qry_LatestResponse.TCC_ID SET tbl_Activity.dtmActioned = [qry_LatestResponse].[LastResponseDate], tbl_Activity.dtmDateClosed = [qry_LatestResponse].[LastResponseDate]
WHERE (((tbl_Activity.dtmActioned) Is Null) AND ((tbl_Activity.Action)='Questionnaire received')) OR (((tbl_Activity.dtmActioned) Is Null) AND ((tbl_Activity.Action)='CC20 Response received'));
Can anyone tell me why this is not an updateable query??
Thanks!
Jim
I have an update query which is not updateable for reasons I cannot fathom.
I have two tables, tbl_Response and tbl_Activity. BOTH have a CaseID field which is indexed Duplicates OK (each case can have several responses and several activity records). I want to update a date field in the Activity table with the date of the latest response for each case.
So I have a select query qry_LatestResponse on the Responses table which GROUPS BY CaseID, with Max(ResponseDate). So CaseID is unique in this query.
In my update query I join my Activity table with qry_LatestResponse on CaseID. All the fields being updated are in the Activity table (i.e. on the many side of the join).
Here is the SQL from my 2 queries...
qry_LatestResponse:
qry_SELECT tbl_CC_Response.TCC_ID, Max(tbl_CC_Response.fResponseDate) AS LastResponseDate
FROM tbl_CC_Response
WHERE (((tbl_CC_Response.ResponseType)="CC01" Or (tbl_CC_Response.ResponseType)="CC20") AND ((tbl_CC_Response.Processed) Is Null))
GROUP BY tbl_CC_Response.TCC_ID;
My update query:
UPDATE tbl_Activity INNER JOIN qry_LatestResponse ON tbl_Activity.TCC_ID = qry_LatestResponse.TCC_ID SET tbl_Activity.dtmActioned = [qry_LatestResponse].[LastResponseDate], tbl_Activity.dtmDateClosed = [qry_LatestResponse].[LastResponseDate]
WHERE (((tbl_Activity.dtmActioned) Is Null) AND ((tbl_Activity.Action)='Questionnaire received')) OR (((tbl_Activity.dtmActioned) Is Null) AND ((tbl_Activity.Action)='CC20 Response received'));
Can anyone tell me why this is not an updateable query??
Thanks!
Jim
Last edited: