sums on a query

[Guest]

Hello,
I’m building a query that I would like to sum quantities of a part numbers
used on multiple Purchase Orders

Example

PO Item # qty sum
16 100 2 5
27 100 1 5
39 100 2 5
19 200 2 7
26 200 5 7

Thanks

 

[Guest]

Use the 'Totals' button in the query designer (see toolbar in query, look for
the E-sign)

Then group the field you want for instance the 'item' field, just above the
criteria field use the arrow for the group by..

hth

 

[B. Edwards]

Something like the following should work (at least it did in Access 2003)

SELECT a.PO, a.Item, a.Qty, (SELECT sum(b.Qty) FROM PurchaseOrders b WHERE
b.Item = a.Item)
FROM PurchaseOrders a
ORDER BY a.Item, a.PO