Operation must use an updateable query workaround

This MS Access problem occurs for many reasons. For some reason, you can't use an aggregate query to update a table. I'm not exactly sure why. Anyhow, some people have proposed working solutions using DLookup... but that method is awkward (especially if you have thousands of records to deal with).

Others have proposed creating a temporary table to store your query results, then running an update query to transfer the results from the temp table to your permanent table. This method works, but the intructions I found online were (with my limited experience) difficult to follow. So I have documented my method below in hopes that it may assist you. Please feel free to post corrections or whatever other input you have.

I created 3 queries; a select query to get the data, a make-table query to store it in a table, and an update query to do the table to table update.

1st: create a normal select query to aggregate all the data you want to update your other table with. We'll call it MYSELECTQUERY. Run the query and make sure it has all the data you want. Look out for duplicated data since this can cause you headaches down the road.

Next: Create a Make-Table query (MYMAKETABLEQUERY) to create a new table called MYTEMPTABLE with the data from MYSELECTQUERY.

3rd: Create an update query (MYUPDATEQUERY) to update MYTARGETTABLE with the data from MYTEMPTABLE.

4th: Test it, then put it all together with code so that a user can execute the update without complication. I set this up as the code behind the click event on a button.

-start code-
DoCmd.SetWarnings False
DoCmd.OpenQuery "MYMAKETABLEQUERY"
DoCmd.OpenQuery "MYUPDATEQUERY"
DoCmd.SetWarnings True
On Error Resume Next
DoCmd.RunSQL "DROP TABLE MYTEMPTABLE"
-end code-

That last line deletes the temporary table (MYTEMPTABLE). The DoCmd.SetWarnings False (and True) lines suppress (and reanable) the prompts Access would normally throw at the user to get them to confirm that they really want to run a query. There is a more elegant want to suppress these prompts, but I can't remember what it is. It's sad.