Hello,
I’m building a query that I would like to sum quantities of a part number
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

Signature
JB
Jason Lepack - 31 Oct 2007 22:07 GMT
Let's say for sanity sake that you have this table:
pos:
po - number
item - number
qty - number
create query to get sums (called sums)
select
item,
sum(qty) as sum_qty
from
po
group by
item
create a new query for your final result:
select
po,
item,
qty,
sum_qty
from
po
inner join sums
on po.item = sums.item
Cheers,
Jason Lepack
> Hello,
> I'm building a query that I would like to sum quantities of a part number
[quoted text clipped - 13 lines]
> --
> JB
Marshall Barton - 31 Oct 2007 22:43 GMT
>Im building a query that I would like to sum quantities of a part number
>used on multiple Purchase Orders
[quoted text clipped - 7 lines]
>19 200 2 7
>26 200 5 7
SELECT [Item #], Sum(qty) As Qsum
FROM thetable
GROUP BY [Item #]
will produce:
100 5
200 7
If you really need the result you posted, then Join that
query back to the table:
SELECT T.PO, T.[Item #], T.qty, Qsum
FROM thetable As T
INNER JOIN (SELECT X.[Item #], Sum(X.qty) As Qsum
FROM thetable As X
GROUP BY X.[Item #])
ON T.[Item #] = X.[Item #]

Signature
Marsh
MVP [MS Access]