Access Update Query using result of a Group By Query

[BiigJiim]

I have a table tblResponses which records responses received for each project in my database. Multiple responses per project, each with a date.

Another table tblActivity stores each activity on a project. Multiple activities per project.

I want to update each record in the Activity table with the date of the MOST RECENT response received for that project. If I use a GROUP BY query on tblResponses to get the Max(ResponseDate) grouped by projectID, I cannot then use this in an update query on tblActivity, as it makes the query not updateable.

At the moment I am having to populate a temporary table from the output of the GROUP BY query, and then use this in the Update query to update tblActivity. Not ideal as leads to database bloat etc, poor performance etc.

Is there any way to do this WITHOUT populating a temporary table? I understand why a Group By query cannot be updateable itself, but don't see why it cannot be used to provide the Update To values for updating another table.

(And yes, I know it shouldn't be necessary to store the result physically in a separate table when it could be calculated, but for various lengthy reasons, that isn't an option here.)

Many thanks for any help! Jim

 

[arnelgp]

if you are unable to update a table from a Total Query, use Domain function instead to update the table.

 

[strive4peace]

hi Jim,

as Pat explained, aggregated data as able to be used in an update query (silly!), and arnelgp suggested, you'll need to use a domain aggregate function to get the value to use. Assuming projectID is numeric, use something like this:

dMax("ResponseDate", "tblResponses", "projectID=" & [projectID])