UPDATE SET Query Fails

[faccorp]

I'm not sure why this query fails

PARAMETERS custid Long
UPDATE CUSTOMERS
SET TOTAL = 
(SELECT SUM(DETAILS.CHARGE) FROM DETAILS WHERE DETAILS.CUSTOMER = custid)
WHERE CUSTOMERS.ID = custid;

I got the error 'operation must be an updatable query'

If I remove the (SELECT SUM() ...) and just us a number then it works fine (i.e. SET TOTAL = 10). Also, my SELECT statement only returns a single value so it should be ok too.


Thanks for any help

 

[HandsOnAccess]

You can try like this...

PARAMETERS custid Long
UPDATE CUSTOMERS
SET TOTAL = DSUM("Charge","Details","Customer = " & CustID)
WHERE CUSTOMERS.ID = custid;

Hope this helps you...

Regards,

 

[faccorp]

Thanks,

but i didn't give the complete query.

Instead of 'FROM DETAILS WHERE DETAILS.CUSTOMER = custid'

it's actually

FROM DETAILS INNER JOIN TRANSACTIONS ON TRANSACTIONS.ID = DETAILS.TRANID WHERE TRANSACTIONS.CUSTOMER = custid


Will the DSUM handle a join expression as the domain.

I've never used DSum before.

 

[HandsOnAccess]

You Can try like this..

Save this part as a query

SELECT SUM(DETAILS.CHARGE) As TotalCharge FROM DETAILS INNER JOIN TRANSACTIONS ON TRANSACTIONS.ID = DETAILS.TRANID WHERE TRANSACTIONS.CUSTOMER = custid

PARAMETERS custid Long
UPDATE CUSTOMERS
SET TOTAL = DLookup("TotalCharge","<<Query Name>>");

Regards,

 

[lespaul]

storing a calculated total breaks normalization rules, is there a valid reason for breaking the rules?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[faccorp]

I did get the DLookup to work to some degree, however, why does my original query not work?

The SELECT statement only returns a single value. According to all my docs, it should work.

Thanks again.