Access SQL UPDATE statement with HAVING clause

Question

I am having trouble with the update statement below. I keep getting missing semicolon as an error anyone knows correct syntax?

Update FactQuote fq 
LEFT JOIN FactQuoteProduct as fqp on fq.spQuoteID=fqp.fk_spQuoteID 
SET fq.spQuoteStatus = 5
WHERE  fq.quoteDate < 5/10/2016
and fq.spQuoteStatus < 4 
and fq.spQuotestatus <>8 
HAVING sum(fqp.ItemTotal)<2000;

Just curious... how can fq.spquotestatus be <4 and <> 8... wouldn't the < 4 suffice? and last time I used access didn't dates have to be wrapped in #'s? `Where fq.quoteDate < #5/10/2016#`? — xQbert, Jun 06 '16 at 18:59
hahah you are right thx, I changed something forgot to remove that — M_kul, Jun 06 '16 at 19:00

JNevill · Answer 1 · 2016-06-06T18:54:21.363

0

You'll have to do your aggregation in a subquery since you can't aggregate and do an UPDATE in a single query. Something like:

Update FactQuote fq 
LEFT JOIN (SELECT fk_spQuoteID, sum(ItemTotal) as itemTotal FROM FactQuoteProduct GROUP BY fk_spQuoteID) as fqp on fq.spQuoteID=fqp.fk_spQuoteID 
SET fq.spQuoteStatus = 5
WHERE  fq.quoteDate < 5/10/2016
and fq.spQuoteStatus < 4 
and fq.spQuotestatus <>8 
and fqp.itemTotal < 2000

edited Jun 06 '16 at 18:54

answered Jun 06 '16 at 18:41

JNevill

46,980
4
38
63

I get operation must use an updateable query with this.can this only work on queries FactQuote is a table in this event – M_kul Jun 06 '16 at 18:53
Access is fickle. [check out these answers](http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access) to see if anything there works. Link tables, saved Update queries, and a few other gotchas that people have work arounds may help out. I believe the syntax here is correct. – JNevill Jun 06 '16 at 18:58
syntax is correct minus renaming itemtotal due to circular reference thx – M_kul Jun 06 '16 at 19:02

score 0 · Accepted Answer · answered Jun 06 '16 at 19:29

You can use a DSum() expression to avoid that "not updateable" complaint.

Also notice the second of these 2 conditions is not needed. When spQuoteStatus < 4, it can't be equal to 8, so adding the condition spQuotestatus <> 8 serves no purpose:

AND fq.spQuoteStatus < 4
AND fq.spQuotestatus <> 8

Assuming spQuoteStatus and fk_spQuoteID are both numeric datatype ...

UPDATE FactQuote AS fq 
SET fq.spQuoteStatus = 5
WHERE
        fq.quoteDate < #2016-5-10#
    AND fq.spQuoteStatus < 4 
    AND DSum("ItemTotal", "FactQuoteProduct", "fk_spQuoteID=" & spQuoteID) < 2000;

If those fields are text datatype, change the last condition to this ...

DSum("ItemTotal", "FactQuoteProduct", "fk_spQuoteID='" & spQuoteID & "'") < 2000

Access SQL UPDATE statement with HAVING clause

2 Answers2