Updating the one table in one to many relationship

[Marv]

I have two tables: Orders and payments. Each order can
have zero to many payments. The order table has the
order number, name & address, amount owed and date
shipped. The payment table has the order number, a
payment reference number (check number for example) and
an amount paid.

An order is filled if the date shipped is filled in AND
the total of the payment amount equals the amount of the
order in the Order table. I want my "Open Orders" form
to display only open orders (ship date blank or sum of
amount paid does not equal amount owed).

How can I do this so I see only open orders and I can add
the date shipped, for example, to make it drop off the
form? Every way I try I get an unupdatable query.

 

[Allen Browne]

Try a subquery in the WHERE clause of the form's RecordSource.
Something like this:

SELECT Orders.*
FROM Orders
WHERE (Orders.[ShipDate] Is Null)
OR (CCur(Nz(
( SELECT Sum([AmountPaid]) As SumOfAmountPaid
FROM Payments
WHERE Payments.OrderID = Orders.OrderID ),0))
<> Orders.[amount owed]);

You can use that WHERE clause as the Filter of your form if you prefer.