Updateable query conundrum (1 Viewer)

[BiigJiim]

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

 

[jdraw]

Please post the table designs and the query SQL so we can see PK, FK etc.

 

[theDBguy]

Hi. Totals queries are not updateable. Besides, your query already shows the matching date, so you don't need to store them again; because, as soon as a new response is entered, the date in the activity table is wrong.

 

[BiigJiim]

Please post the table designs and the query SQL so we can see PK, FK etc.

Hi jdraw,

I have edited my post to include the sql from the queries. I don't know how to export the tabledefs as a text file, but both tables have an ID primary key and both tables have an Index on TCC_ID set to Allow Duplicates. (TCC_ID is the CaseID)

 

[BiigJiim]

Hi. Totals queries are not updateable. Besides, your query already shows the matching date, so you don't need to store them again; because, as soon as a new response is entered, the date in the activity table is wrong.

Hi DBguy,

I know totals queries are not updateable. I am only using the totals query to get the latest response date for each case. The update query is updating fields in tbl_Activity on the many side of the join.

 

[theDBguy]

Hi DBguy,

I know totals queries are not updateable. I am only using the totals query to get the latest response date for each case. The update query is updating fields in tbl_Activity on the many side of the join.

Right. But as soon as you join a query to a non updateable query, then the whole query becomes not updateable. What about what I said regarding incorrect dates in the table?

 

[jdraw]

dbGuy is suggesting that storage of the Max(date..) into the activity table is redundant and the value will be in error whenever a change to the related response record is made. If my interpretation of his comment is correct, then I agree that you may storing something that should be calculated (via select query) from your data when needed.

 

[BiigJiim]

I hear what you are saying, but the database was already designed this way before I took it on. There are several forms which are bound to the Activity table, and the database is operating over a slow network with far too many users, so it is easier to just have an extra field in the activity table. Responses are added only by an automated process, which updates the Activity date field as part of that process, and never edited so the two should always be in alignment (!!)

 

[Bullschmidt]

Well I suppose in general if you had to revise your approach to something that actually works (updateable) you could in code loop through all the records of the totals query and for each row run an update query for just that one record...

 

[BiigJiim]

I ended up populating a temp table from the group by query, with a unique index on the Case ID, and then using this temp table in the update query. Works fine, but I would have thought Access would be smart enough to know if you are grouping on a single field, that field would be unique. I had the feeling I had done this loads of times before without problem so thought I must be doing something daft!