BOM Query / Sql help required for a rookie

[TIMER]

Hi

I'm trying to create a totaled/calculated BOM Query for each of my bomID's. I have no knowledge of SQL but came across a DISTINCT SQL function while on the web and wondered if this is what I should be using to get my desired result below.

product_TBL
productID
productDESC
costpricePU

bom_TBL
bomID
bomDESC

components_TBL (my intermediate junction table)
bomID
bomDESC
productQTY



What I'm currently getting from my current Query.....
**Note: The Total Cost Column is a calculated field within my query

SQL....

SELECT bom_TBL.bomID, bom_TBL.description, products_TBL.productID, components_TBL.productQTY, products_TBL.costpricePU, [productQTY]*[costpricePU] AS [Total Cost]
FROM bom_TBL INNER JOIN (products_TBL INNER JOIN components_TBL ON products_TBL.productID = components_TBL.productID) ON bom_TBL.[bomID] = components_TBL.BomCode
GROUP BY bom_TBL.bomID, bom_TBL.description, products_TBL.productID, components_TBL.productQTY, products_TBL.costpricePU, [productQTY]*[costpricePU];


What I would like to get from this Query or Second Query.... Note that the image below was modified to get that result.

Can any one help?

Kind Regards

Fergus

 

[plog]

You need an aggregate query. Use the query you have as the basis as another query and then Group on the fields that you want to be distinct within this new query and then Sum the values you want totals for. This is the SQL you will need:

SELECT bomID, bomDESC, SUM([Total Cost]) AS SumOfTotalCost FROM YourQueryNameHere GROUP BY bomID, bomDESC ORDER BY bomID, bomDESC;

Replace 'YourQueryNameHere' with the name of the query you posted above and it should generate the results you want.

 

[TIMER]

I see now what I was doing incorrectly. I was grouping (Group By) my productID, productQTY & costpricePU.

and I should have been doing this >>>>>>

Thanks Plog for your help.

Regards

Fergus