Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / October 2007

Tip: Looking for answers? Try searching our database.

sums on a quary

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
matjcb - 31 Oct 2007 21:22 GMT
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
>I’m 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]

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.