MS Access SQL强制不存在的值

Question

要概述，我将每周的数字营销收入分为总数的百分比，以渠道/bu/bu/order_store/item_store/item_store的销售额为单位到创建比率。

该方程式的困境是，如果由于给定的频道/bu/order_store/item_store/week中的不存在销售而缺少比率，我的支出查询将乘以零，这将低估支出。如果是这种情况，我需要通过BU销售量将支出迫使支出进入" Sears" Item_store。我处于十字路口，因此可以与将此解决方案转换为SQL编码：

创建比率

SELECT a.*, CDbl(a.Revenue / b.Revenue) AS Ratios
FROM 
(SELECT buc.FY, buc.FM, buc.FW, buc.Rept_Chnl, buc.BU_NM,
IIf(buc.Order_Store="SEARS","Desktop","Mobile") AS Platform, buc.Item_Store, SUM(buc.Revenue) AS Revenue 
FROM tbl_BUChannelReporting AS buc 
WHERE buc.order_Store in ("SEARS","MG:SRS")  
AND buc.Item_Store <> "MKTPL" 
GROUP BY buc.FY, buc.FM, buc.FW, buc.Rept_Chnl, buc.BU_NM, IIf(buc.Order_Store="SEARS","Desktop","Mobile"), buc.Item_Store 
HAVING SUM(buc.Revenue) <> 0)  AS a 
INNER JOIN 
(SELECT buc.FY, buc.FM, buc.FW, buc.Rept_Chnl, buc.BU_NM, IIf(buc.Order_Store="SEARS","Desktop","Mobile") AS Platform, SUM(buc.Revenue) AS Revenue 
FROM tbl_BUChannelReporting AS buc 
WHERE buc.order_Store in ("SEARS","MG:SRS")  AND buc.Item_Store <> "MKTPL" 
GROUP BY buc.FY, buc.FM, buc.FW, buc.Rept_Chnl, buc.BU_NM, IIf(buc.Order_Store="SEARS","Desktop","Mobile") 
HAVING SUM(buc.Revenue) <> 0)  AS b 
ON (a.FY = b.FY) 
AND (a.FM = b.FM) 
AND (a.FW = b.FW) 
AND (a.Rept_Chnl = b.Rept_Chnl) 
AND (a.BU_NM = b.BU_NM) 
AND (a.Platform = b.Platform);

将支出乘以比率（从先前查询）

SELECT b.FY, b.FM, b.FW, b.Omniture_Channel, b.BU_NM, b.Platform, b.item_store, cdbl(b.Ratios * a.Spend) AS Spend
FROM 
(SELECT s.FY, s.FM, s.FW, s.Omniture_Channel, s.BU_NM, s.Platform, sum(s.Spend) AS Spend 
FROM spend_kmart AS s 
GROUP BY s.FY, s.FM, s.FW, s.Omniture_Channel, s.BU_NM, s.Platform 
HAVING SUM(s.Spend) <> 0)  AS a 
INNER JOIN 
(SELECT val(i.FY) AS FY, i.FM, i.FW, i.Rept_Chnl AS Omniture_Channel, i.BU_NM, i.Platform, i.item_store, cdbl(i.Ratios) AS Ratios 
FROM qry_Item_Store_Ratios_KMT_Non_MKTPL AS i 
GROUP BY val(i.FY), i.FM, i.FW, i.Rept_Chnl, i.BU_NM, i.Platform, i.item_store, cdbl(i.Ratios))  AS b 
ON (a.FY = b.FY) 
AND (a.FM = b.FM) 
AND (a.FW = b.FW) 
AND (a.Omniture_Channel = b.Omniture_Channel) 
AND (a.BU_NM = b.BU_NM) 
AND (a.Platform = b.Platform);

• 另一个注意事项是，在第一个查询中，我添加了" sum（buc.revenue）＆lt;> 0"子句，因为我得到了num＆amp;DIV/0错误。

示例

事先感谢您的时间并保重！

-d

Answer 1

查询无法创建不存在的数据。如果您需要显示频道/bu/order_store/item_store/Week的所有组合，即使没有数据，也需要一个数据集的数据集，以了解通道/bu/bu/order_store/item_store/week的所有可能组合。如果您有这些元素中的每个元素（包括几周）的表格，则可以通过包括它们在内的查询创建此数据集。此查询将没有任何加入条款，这会导致每个表与其他表格的每条记录相关联的每张记录 - 笛卡尔产品查询。当心，桌子越慢，查询速度就慢。然后将此查询加入销售数据表。

或另一种方法是具有DSUM（）函数的52个文本框。我已经使用这种方法来构建一个简单的报告，显示了12个月的汇总数据。我希望每个月都可以显示，即使没有数据。