SQL Query - I want the sum of a field.......

[Richard Horne]

I have the following query:

SELECT Deliveries.OrderNumber, Orders.OrderNumber,
Delivery_Details.DeliveryNoteID, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Denomination,
Delivery_Details.ItemNumber, Delivery_Details.QtyDelivered, Deliveries.Date,
Deliveries.Despatch, Products.Product, Denominations.Denom
FROM Company_Details INNER JOIN ((Denominations INNER JOIN ((Orders INNER
JOIN (Delivery_Details INNER JOIN Order_Details ON
Delivery_Details.ItemNumber = Order_Details.[Item Number]) ON
Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Deliveries ON
(Delivery_Details.DeliveryNoteID = Deliveries.DeliveryNoteID) AND
(Orders.OrderNumber = Deliveries.OrderNumber)) ON Denominations.denomid =
Order_Details.Denomination) INNER JOIN Products ON Order_Details.[Item Type]
= Products.Id) ON Company_Details.ID = Orders.[Customer Name]
WHERE (((Deliveries.OrderNumber)=[Orders].[OrderNumber])) ORDER BY
Orders.OrderNumber, Order_Details.[Item Number];

Apologies for the length and 'complicatedness' of it. Can anyone tell me how
to display this query such that the field Delivery_Details.QtyDelivered is
totalled up and displayed instead of each record and the different Qtys
Delivered.

 

[Guest]

hi,
sorry. you cann't do that with this query. You can use the
aggragate function SUM but with the group by clause on all
of the other data, all of the details would be dragged
into the query results.
You can do that in a report but you would have all of the
details with a total at the bottom.
to get the SUM of the of Delivery_Details.QtyDelivered you
would have to eliminate all of the other fields and just
have, say, Delivery_Details.ItemNumber and
Delivery_Details.QtyDelivered with GROUP BY ItemNumber
and SUM QtyDelivered. you could have a where clause the
the date as criteria but you would be able to display the
date in the query because it would group by the date.
this would produce a list of item number and the total qty
delivered for each item and if you had date criteria, the
results would be for that criteria.
Sorry it's not the answer you want.
good luck.

-----Original Message-----
I have the following query:

SELECT Deliveries.OrderNumber, Orders.OrderNumber,
Delivery_Details.DeliveryNoteID, Orders.[Customer Name], Orders.[Order Date],
Order_Details.[Item Number], Order_Details.[Item Type],
Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Denomination,
Delivery_Details.ItemNumber,

Delivery_Details.QtyDelivered, Deliveries.Date,

Deliveries.Despatch, Products.Product, Denominations.Denom
FROM Company_Details INNER JOIN ((Denominations INNER JOIN ((Orders INNER
JOIN (Delivery_Details INNER JOIN Order_Details ON
Delivery_Details.ItemNumber = Order_Details.[Item Number]) ON
Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Deliveries ON
(Delivery_Details.DeliveryNoteID = Deliveries.DeliveryNoteID) AND
(Orders.OrderNumber = Deliveries.OrderNumber)) ON Denominations.denomid =
Order_Details.Denomination) INNER JOIN Products ON Order_Details.[Item Type]
= Products.Id) ON Company_Details.ID = Orders.[Customer Name]
WHERE (((Deliveries.OrderNumber)=[Orders].[OrderNumber])) ORDER BY
Orders.OrderNumber, Order_Details.[Item Number];

Apologies for the length and 'complicatedness' of it. Can anyone tell me how
to display this query such that the field

Delivery_Details.QtyDelivered is