Access 2010 SQL query select inner join with temporary tables

Question

In Access 2013 using VBA I am running an SQL query where a table (CUSTOMERS_TEMP) has a monthly_total which is the sum of sales_price in a temporary table (SALESHIST) taken from a SALESHIST_TEMP table. I'm not sure why this is not working.

Dim SQL As String
SQL = "Update CUST " _
    & "SET CUST.Monthly_Total = SALESHIST.Monthly_Total " _
    & "FROM CUSTOMERS_TEMP AS CUST " _
    & "INNER JOIN (SELECT KEY, SUM(SALES_PRICE) AS MONTHLY_TOTAL FROM SALESHISTORY_TEMP GROUP BY KEY) AS SALESHIST " _
    & "ON CUST.Key = SALESHIST.Key "
DoCmd.RunSQL SQL

Answer 1

Consider DSum domain aggregate for an updateable query. Also consider saving your query as stored object in database as it is more efficient since Access engine can compile it for best execution plan. Also, avoid Key for column name as it is a reserved word.

SQL

UPDATE CUSTOMERS_TEMP CUST 
SET CUST.Monthly_Total = DSum("SALES_PRICE", "SALESHISTORY_TEMP", 
                              "[Key]='" & CUST.[Key] & "'")

VBA

CurrentDb.Execute "mySavedQuery", dbFailOnError

Finally, reconsider saving aggregate data in source tables as you can always query them as needed and avoid the storage resource.