Percentage of Total

[uaguy3005]

I have the following query I would like to add a calculated field that takes the SumOfQtyRejected number and divide it by the total of all the rejects tallied in this query. Hopefully that's not too vague



SELECT [tblParts].[PartNum] & " - " & [DefectDescription] AS Issue, qzTotInsp.SumOfQtyInspected, Sum(tblRejects.QtyRejected) AS SumOfQtyRejected, [SumOfQtyRejected]/[SumOfQtyInspected]*1000000 AS PPM
FROM ((tblParts INNER JOIN qzTotInsp ON tblParts.PartID = qzTotInsp.PartID) INNER JOIN tblSortData ON tblParts.PartID = tblSortData.PartsID) INNER JOIN (tblDefectCodes INNER JOIN tblRejects ON tblDefectCodes.DefectID = tblRejects.DefectCode) ON tblSortData.SortDataID = tblRejects.SortDataID
GROUP BY [tblParts].[PartNum] & " - " & [DefectDescription], qzTotInsp.SumOfQtyInspected

All help appreciated

[ranman256]

Make a query to sum the values, qsTotals.
make a new query, qsPct, that uses the data table and qsTotals, that has the totals so you can sum by item,and divide to get pct.

[uaguy3005]

Make a query to sum the values, qsTotals.
make a new query, qsPct, that uses the data table and qsTotals, that has the totals so you can sum by item,and divide to get pct.

What would qsTotals look like? I cant seem to figure out how to get that related back to the query asPct?

[June7]

qsTotals would be an aggregate (GROUP BY) Totals query. Use the query builder.

Alternatively, build a report and use its Sorting & Grouping feature with aggregate calcs in report and group header/footer textboxes.