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

[Richard Horne]

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];

 

[Lynn Trapp]

Try this instead:

SELECT Orders.OrderNumber, Order_Details.[Item Number],
Order_Details.Quantity, Sum(Delivery_Details.QtyDelivered) AS
SumOfQtyDelivered, Order_Details.Quantity -
Sum(Delivery_Details.QtyDelivered) AS RemainingQty
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
ORDER BY Orders.OrderNumber, Order_Details.[Item Number];


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Richard Horne]

Lynn thank you ever so much. That worked a treat - I was along the right
lines but just didn't quite have the correct syntax - this has been driving
me crackers for the last few days.

A massive massive thank you!

 

[Lynn Trapp]

You are very welcome. You might want to get a copy of SQL for Mere Mortals
by Michael Hernandez and John Viescas and devour it. You'll be glad you
did.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

Lynn thank you ever so much. That worked a treat - I was along the right
lines but just didn't quite have the correct syntax - this has been
driving
me crackers for the last few days.

A massive massive thank you!

Try this instead:

SELECT Orders.OrderNumber, Order_Details.[Item Number],
Order_Details.Quantity, Sum(Delivery_Details.QtyDelivered) AS
SumOfQtyDelivered, Order_Details.Quantity -
Sum(Delivery_Details.QtyDelivered) AS RemainingQty
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
ORDER BY Orders.OrderNumber, Order_Details.[Item Number];


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html