sums on a quary

[Guest]

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

 

[Jason Lepack]

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

 

[Marshall Barton]

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

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