Query - sum by Item Number, is it possible?

[Guest]

I have a query for which the SQL code is:

SELECT Deliveries.DeliveryNoteID, Deliveries.OrderNumber,
Delivery_Details.ItemNumber, Delivery_Details.QtyDelivered,
Delivery_Details.DeliveryDate
FROM Deliveries INNER JOIN Delivery_Details ON Deliveries.DeliveryNoteID =
Delivery_Details.DeliveryNoteID
WHERE (((Deliveries.OrderNumber)=[Forms]![Deliveries]![OrderNumber]));

The results look something like:
The columns are DeliveryNoteID OrderNumber, ItemNumber, QtyDelivered,
DeliveryDate

2 76 1 50 17/02/2005
2 76 2 2000 17/02/2005
4 76 1 2000 17/02/2005
4 76 2 2500 17/02/2005
8 76 2 500 18/02/2005

How can I get this query so that Item numbers are grouped together and the
sum of QtyDelivered for each item number is displayed? I want to do this
because I want to compare the total number for each item delivered with the
original number ordered so I can calculate the remaining quantity.

 

[PC Datasheet]

Create another query based on this query. Only include the ItemNumber and
QuantityDeliveded. With the query in design view, click on the Sigma (looks
like E) button on the toolbar at the top of the screen. Under QtyDelivered,
change Group By to Sum. When you run the query you will get the results you
want.

 

[Guest]

hi,
SELECT Delivery_Details.ItemNumber,
SUM(Delivery_Details.QtyDilivered) as TotDelivered
FROM Diliveries INNER JOIN Delivery_Details ON
Deliveries.DeliveryNoteID = Delivery_Details.DeliveryNoteID
GROUP BY Delivery_Details.ItemNumber
WHERE (((Deliveries.OrderNumber)=[Forms]![Deliveries]!
[OrderNumber]));
This eliminated 3 fields form you query. the OrderNumber
in on the form and not needed in the query. the date and
Delivery Note ID had to be eliminated because they have
diffent values.
We are using an aggragate function (SUM) with GROUP BY and
the query will sum by the GROUP BY. If we had added date,
it would also grouped on the date and sum Items delivered
on that date and not the total delivered for the order.
If you need all of the info in the query AND the sum, then
you may look into attaching the query you have now to a
report.

 

[Guest]

Hi your code below didn't work - I noticed your mispelt deliveries, but even
changing that didn't work.

Also I need the query to display the delivery date and deliveryNoteID so I
can see what was delivered when. Finally you removed the OrderID from the
query, but this has to be in there as the query runs based on the OrderID.

Any other suggestions?

hi,
SELECT Delivery_Details.ItemNumber,
SUM(Delivery_Details.QtyDilivered) as TotDelivered
FROM Diliveries INNER JOIN Delivery_Details ON
Deliveries.DeliveryNoteID = Delivery_Details.DeliveryNoteID
GROUP BY Delivery_Details.ItemNumber
WHERE (((Deliveries.OrderNumber)=[Forms]![Deliveries]!
[OrderNumber]));
This eliminated 3 fields form you query. the OrderNumber
in on the form and not needed in the query. the date and
Delivery Note ID had to be eliminated because they have
diffent values.
We are using an aggragate function (SUM) with GROUP BY and
the query will sum by the GROUP BY. If we had added date,
it would also grouped on the date and sum Items delivered
on that date and not the total delivered for the order.
If you need all of the info in the query AND the sum, then
you may look into attaching the query you have now to a
report.

-----Original Message-----
I have a query for which the SQL code is:

SELECT Deliveries.DeliveryNoteID, Deliveries.OrderNumber,
Delivery_Details.ItemNumber, Delivery_Details.QtyDelivered,
Delivery_Details.DeliveryDate
FROM Deliveries INNER JOIN Delivery_Details ON Deliveries.DeliveryNoteID =
Delivery_Details.DeliveryNoteID
WHERE (((Deliveries.OrderNumber)=[Forms]![Deliveries]! [OrderNumber]));

The results look something like:
The columns are DeliveryNoteID OrderNumber, ItemNumber, QtyDelivered,
DeliveryDate

2 76 1 50 17/02/2005
2 76 2 2000 17/02/2005
4 76 1 2000 17/02/2005
4 76 2 2500 17/02/2005
8 76 2 500 18/02/2005

How can I get this query so that Item numbers are grouped together and the
sum of QtyDelivered for each item number is displayed? I want to do this
because I want to compare the total number for each item delivered with the
original number ordered so I can calculate the remaining quantity.


.