Update query based on calculated field

[MajP]

I have two tables, one is a Purchase table with contains the purchase price of an item. The second is a payment table with all of the payments for that items. If the sum of the payments are greater or equal to the purchase cost, I would like to update the purchase table.

tblPurchase
autoPurchaseID "the Purchase ID"
currCost "the price of the item"
blnPaidOff "boolean field for if it is paid off"

tblPayments
autoIDPayment "the payment ID"
currPayment " the amount of the payment"
fkPurchase "the FK that links to the Purchase ID"

If all of my payments are greater then the purchase cost then I would like to run an update query to mark the "blnPaidOff" field to true.

I know how to calcuate the total payments:
SELECT tblPurchase.autoPurchaseID, tblPurchase.currCost, Sum(tblPayment.currPayment) AS SumOfcurrPayment
FROM tblPayment INNER JOIN tblPurchase ON tblPayment.fkPurchase = tblPurchase.autoPurchaseID
GROUP BY tblPurchase.autoPurchaseID, tblPurchase.currCost;

I know how to figure out dynamically if the item is paid off.
SELECT qrySumOfPayments.autoPurchaseID, IIf([qrySumOfPayments]![SumOfcurrPayment]>=[qrySumOfPayments]![currCost],True,False) AS calcPaidOff
FROM qrySumOfPayments;

But I can not figure how to run an update query from here on the tblPurchase. I can not figure how to design an updateable query.

Although it is a calculated value, and I should not need to store it, it would make doing some other reports simpler.

Thanks.

 

[PHV]

One possible way:
UPDATE tblPurchase
SET blnPaidOff = True
WHERE DSum("currPayment", "tblPayments", "fkPurchase=" & [autoPurchaseID]) >= currCost

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

 

[MajP]

Thanks,
I will go with that. I was trying to avoid using a DSum function. I have never experienced much problem with the vb domain aggregate functions, but I try to avoid them when I can because I read posts claiming that they can be problematic and slow.