Create one query with sum and count with each value pulled from a different table

Question

I am trying to create a query that pulls two different aggregated values from three different tables during a specific date range. I am working in Access 2003.

I have:

tblPO which has the high level purchase order description (company name, shop order #, date of order, etc)

tblPODescription which has the dollar values of the individual line items from customers the purchase order

tblCostSheets which as a breakdown of the individual pieces that we need to manufacture to satisfy the customers purchase order.

I am looking to create a query that will allow me, based on the Shop Order #, to get both the sum of the dollar values from tblPODescriptions and the count of the different type of pieces we need to make from tblCostSheets.

A quick caveat: the purchase order may have 5 line items for a sum of say $1560 but it might take us making 8 or 9 different parts to satisfy those 5 line items. I can easily create a query that pulls either the sum or the count by themselves, but when I created my query with both, I end up with numbers that are multipled versions of what I want. I believe it is multiplying my piece counts and dollar values.

SELECT DISTINCTROW tblPO.CompanyName, tblPO.ShopOrderNo, tbl.OrderDate, Sum(tblPODescriptions.ItemAmount) AS SumOfItemAmount, Count(tblCostSheets.Description) AS CountOfDescription
FROM (tblPO INNER JOIN tblPODescriptions ON (tblPO.CompanyName = tblPODescriptions.CompanyName) AND (tblPO.PurchaseOrderNo = tblPODescriptions.PurchaseOrderNo) AND (tblPO.PODate = tblPODescriptions.PODate)) INNER JOIN tblCostSheets ON tblPO.ShopOrderNo = tblCostSheets.ShopOrderNo
GROUP BY tblPO.CompanyName, tblPO.ShopOrderNo, tblPO.OrderDate
HAVING (((tblPO.OrderDate) Between [Enter Start Date:] And [Enter End Date:]));

*"I can easily create a query that pulls either the sum or the count by themselves"* In that case create one query for the sums and a second query for the counts. Create a third query which uses the first two as its data sources and joins them together based on `ShopOrderNo`. — HansUp, Oct 31 '13 at 16:25
If you can join those two queries together, you should be good to go. And you'll want to change that HAVING to a where clause. Having checks after aggregation, so you would use it if you wanted to see POs that add up to over 1000 dollars, something like that. If you want to restrict what rows are retrieved, you put in a where clause. — Andrew, Oct 31 '13 at 16:25
I want to use the final query as a basis for a report that shows the Shop Order #, the $ for the order and the # of pieces to make. If I use the 3 query option will the 3rd query automatically cause the first 2 to run or would they need to be run by themselves first. I am trying to automate this report for a coworker and want to keep it as a "one-click" report for him. — Scott, Oct 31 '13 at 21:17

Create one query with sum and count with each value pulled from a different table

0 Answers0