Update Query problem

[Guest]

I'm trying to write an update query, but am stuck because the criteria
required make the query non-updateable.

tblTask:

TaskID (pk)
TaskName
Price
Completed

tblTransaction:

TaskID (fk)
TransactionID (pk)
EmployeeID
Authorised

I want to update the completed field. If SumOfAuthorised > Price, Completed
= 1, else Completed = SumOfAuthorised / Price.

The problem is, as soon as I use a totals query (or evidently if I use a
Sum), the query becomes non-updateable. Any ideas?

Thanks,

Dave

 

[John Spencer]

You need to use aggregate functions in this case.

UPDATE TblTask
SET Completed = Abs(DSum("Authorised","tblTransAction","TaskId =" & Chr(34)
& tblTask.TaskID & Chr(34)) > tblTask.Price)

If taskId is a number field then remove the & Chr(34) delimiters.

Since there are only two values to set you could use two queries to set the
values. You would need to use a subquery in the where clause of each of
those queries to identify which taskId to update.