UPDATE Query Fails

[faccorp]

Does anybody know 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 get the error 'operation must use an updateable query' however if I execute without the SELECT command, then it works, and the select command is returning only 1 item.

This should work! I am at a loss.

 

[PHV]

You may try this:
PARAMETERS [custid] Long;
UPDATE CUSTOMERS
SET TOTAL = DSum("CHARGE", "DETAILS", "CUSTOMER=" & [custid])
WHERE ID = [custid];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[faccorp]

Yes. Thank you. I have tried it with some success.

I am still wondering my original query fails. I have many querys like this one that I am converting from a SQL Server database. This query should work under Access 2000.

Any ideas why the query fails as written?

Thanks again.

 

[dhookom]

Access SQL is not the same as every other flavor of SQL. Generally, if you have a totals query anywhere in an Access query, it will not be updateable.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[faccorp]

Thank

I have also simplified the query to see if it would update a basic Integer field using something like:

PARAMETERS custid Long
UPDATE CUSTOMERS
SET NUMFIELD = 
(SELECT OTHERNUMFIELD FROM DETAILS WHERE DETAILS.CUSTOMER = custid)
WHERE CUSTOMERS.ID = custid;

The SELECT only returns one value (so it's not a one-many relationship)... still it fails

 

[dhookom]

I think you could possibly create an INNER JOIN query on CUSTOMERS and DETAILS and do an update. This would require DETAILS.CUSTOMER and CUSTOMERS.CustID to have a primary/foreign key relationship.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]