0

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

BiigJiim
  • 165
  • 1
  • 1
  • 10
  • Sounds similar to what you want: https://stackoverflow.com/questions/5272412/group-by-in-update-from-clause – Marcucciboy2 Apr 01 '20 at 12:36
  • Does this answer your question? [GROUP BY in UPDATE FROM clause](https://stackoverflow.com/questions/5272412/group-by-in-update-from-clause). Or use VBA and loop recordset. – June7 Apr 01 '20 at 16:11

0 Answers0