Subtract one field from another to make a third field in a query?

[HairyArse]

Hi guys, I have a query containing the following fields:

OrderNumber, ItemNumber, QuantityOrdered and QuantityDelivered

QuantityDelivered is the SUM of all Deliveries against each Item in an order.
I'm trying to generate another field called QuantityRemaining which is equal
to:

QuantityOrdered-SUM(QuantityDelivered)

But this does not seem to work, what should I put as my criteria for
QuantityRemaining?

My SQL Code is:
SELECT Orders.OrderNumber, Order_Details.[Item Number],
Order_Details.Quantity, Sum(Delivery_Details.QtyDelivered) AS
SumOfQtyDelivered, [Remaining] AS Expr1
FROM Delivery_Details INNER JOIN ((Orders INNER JOIN Order_Details ON
Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Deliveries ON
Orders.OrderNumber = Deliveries.OrderNumber) ON
(Delivery_Details.DeliveryNoteID = Deliveries.DeliveryNoteID) AND
(Delivery_Details.ItemNumber = Order_Details.[Item Number])
GROUP BY Orders.OrderNumber, Order_Details.[Item Number],
Order_Details.Quantity, [Remaining]
HAVING ((([Remaining])=("Quantity")-Sum("QtyDelivered")))
ORDER BY Orders.OrderNumber, Order_Details.[Item Number];

 

[HairyArse]

I'm getting closer to solving this:

As a new filed in my query I have:

QuantityRemaining: [Quantity]-[QtyDelivered]

Which works, but I need this to be for the Sum of Qty Delivered.

If I put

[Quantity]-Sum[QtyDelivered] I get the error 'The expression you entered contains invalid syntax'

How do I fix this?

 

[Birdy]

Try this.....

You will possibly need to run a query within a query.

Query 1 to get the Sum of the Qty Delivered.

Query 2. Using Query 1 and the original tables, you will then be able to use the following :-
QuantityRemaining: [Quantity]-[SumOfQtyDelivered]




Hope this helps.