Access Update Query using result of a Group By Query (1 Viewer)

[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

 

[Pat Hartman]

The reason that storing this data is a violation of normal forms is because as soon as someone enters a new record, your saved calculated data is WRONG. The only way it is even viable to store calculated values is if you intend to update the table ONCE (and in this case, why does it matter if you have to make a temp table to do it) and you have ALREADY changed ALL the necessary places in the application to update the calculated field when the source data changes.

There is no "efficient" way to do this in a relational database because doing so violates normal forms and therefore would never be supported.

The only place you will regularly see calculated data stored is in a data warehouse. Data warehouses are typically rebuilt weekly, monthly, whatever schedule makes sense for your reporting and they are built using append queries that aggregate the data. So, an append query can aggregate data and add rows to a table. It is only update queries that cannot use aggregated data.

I happen to agree that Access could be smart enough to allow updates as long as there is no ambiguity in the target record but it doesn't. It simply marks every query that aggregates any data at all or joins to any query that aggregates data as not updateable.

 

[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])