Update Query

[Guest]

I am trying to update a field of some records in a table but it tells me the
query is not an updateable query. The issue is I need to update the records
if the sum of field [amount] = 0 grouped by the [invoice] field. If the sum
= 0 then for each record of that [invoice] the field [MonthClearing] should
be updated to: Year-Month for example 2005-04. I tried to do a TotalQuery
sum and return the invoices whose total amount = 0 and then create another
query where I Join the TotalQuery to the Table and join on the invoice number
for only including items where the invoices are equal and then Updating the
[MonthClearing] in the table. This is where it tells me it is not an
updateable query. How do I work out this issue.

Thank you for your help.

Steven

 

[Steve Schapel]

Steven,

Try your Update Query along these lines...

UPDATE YourTable SET [MonthClearing]="2005-04"
WHERE [invoice] In(SELECT [invoice]
FROM YourTable
GROUP BY [invoice]
HAVING Sum([amount])=0)