将记录计数显示为交叉表查询Access SQL中记录组总数的百分比。

Question

我在access中构建一个离线DB，当对数据进行分组时，我对公司ID进行分组，然后按日期，然后对Prelim大小进行分组。然后还有进一步的分类来显示每条记录的破损情况。断点中有5种类型，然后使用pivot表将其转换为列标题。

我需要计算一个子组的记录计数属于这个组的百分比。

我有生成结果的枢轴表，但我所缺少的是显示该数字在该行总数中所占的百分比。所以在第一个记录中，我想把5+2+2+3加起来，这是15，并将每个值显示为15的百分比。

目前的表格结果：

在这里输入图像描述

Bag_Company_ID，Shift_Date，Prelim_Size，1-未断，2片，3-轻微破碎，4-严重破碎，5-片段

2016/05/20 10 Ct 5 2 2 3

2016/05/20 3-4 Ct 4 5 1 3 5

2016/05/20 3-6 Gr 1 2 1 3 0

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;

Answer 1

您将需要使用交叉表作为源进行额外的查询。交叉表是一种特殊的查询，除了整个聚合之外，不允许在旋转值中的表达式。但是，在这样做之前，添加Count(list_Breakages.Class_Cat)作为第二个查询中的分母：

交叉表查询

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, Count(list_Breakages.Class_Cat) As TotalCatCount
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;

Pct查询

SELECT c.Bag_Company_ID, c.Shift_Date, c.Prelim_Size,
       (c.[1 - Unbroken] / c.TotalCatCount) As c.[1-Unbroken_Pct], 
       (c.[2 - Chipped] / c.TotalCatCount) As c.[2-Chipped_Pct], 
       (c.[3 - Lightly Broken] / c.TotalCatCount) As c.[3-Lightly-Broken_Pct],  
       (c.[4 - Heavily Broken] / c.TotalCatCount) As c.[4-Heavily-Broken_Pct], 
       (c.[5 - Fragment] / c.TotalCatCount) As c.[5-Fragment_Pct]
FROM CrossTabQuery c

或者，考虑一个条件聚合查询，特别是考虑到您的旋转列只有几个5。这通常是大多数RDMS中使用的枢轴查询形式(因为交叉表是MS Access SQL特有的)。在这里，分层表达式是允许的。如果您的列值运行20+，请考虑以上第二种查询方法：

SELECT tbl_Bags.Bag_Company_ID, Format([Audit_PreVal_Date],"Short Date") AS Shift_Date, 
       Client_Parcels.Prelim_Size,
       SUM(IIF(list_Breakages.Class_Cat = '1 - Unbroken', 1, 0)) / 
           Count(list_Breakages.Class_Cat) AS [1 - Unbroken],
       SUM(IIF(list_Breakages.Class_Cat = '2 - Chipped', 1, 0)) / 
           Count(list_Breakages.Class_Cat) AS [2 - Chipped],
       SUM(IIF(list_Breakages.Class_Cat = '3 - Lightly Broken', 1, 0)) / 
           Count(list_Breakages.Class_Cat) AS [3 - Lightly Broken],
       SUM(IIF(list_Breakages.Class_Cat = '4 - Heavily Broken', 1, 0)) / 
           Count(list_Breakages.Class_Cat) AS [4 - Heavily Broken],
       SUM(IIF(list_Breakages.Class_Cat = '5 - Fragment', 1, 0)) / 
           Count(list_Breakages.Class_Cat) AS [5 - Fragment] 
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;