Showing a record count as a percentage of totals of groups of records in a crosstab query, Access SQL
<p>I'm building an offline DB in access and when grouping data, I group on Company ID, then Date, then Prelim Size. Then there's a further classification to show breakages for each record. There are 5 categories in breakages, which are then transformed into column headings using a pivot table. </p>

<p>I need to calculate the percentage that the record count of a sub-group is of that group.</p>

<p>I have the pivot table producing the results, but what I am missing is to show that number as a percentage of the total of that row. So on the first record, I want to add up 5 + 2 + 2 + 3 + 3 which is 15, and show each of those values as a percent of 15.</p>

<p>Current table result:</p>

<p><a href="http://i.stack.imgur.com/TQ54K.png" rel="nofollow">enter image description here</a></p>

<p>Bag_Company_ID, Shift_Date, Prelim_Size,    1 - Unbroken,   2 - Chipped,    3 - Lightly Broken, 4 - Heavily Broken, 5 - Fragment</p>

<p>10  2016/05/20  10 Ct       5   2   2   3   3</p>

<p>10  2016/05/20  3 - 4 Ct    4   5   1   3   5</p>

<p>10  2016/05/20  3 - 6 Gr    1   2   1   3   0</p>

<pre><code>TRANSFORM Count(list_Breakages.Class_Cat) AS Cat
SELECT tbl_Bags.Bag_Company_ID, Format([Audit_PreVal_Date],"Short Date") AS Shift_Date, Client_Parcels.Prelim_Size
FROM list_Breakages INNER JOIN (tbl_Bags INNER JOIN Client_Parcels ON tbl_Bags.Bag_ID = Client_Parcels.Bag_ID) ON list_Breakages.Breakages_ID = Client_Parcels.Breakages
WHERE (((Client_Parcels.Prelim_Size) Is Not Null))
GROUP BY tbl_Bags.Bag_Company_ID, Format([Audit_PreVal_Date],"Short Date"), Client_Parcels.Prelim_Size
PIVOT list_Breakages.Class_Cat;
</code></pre>