访问产品经理能力时间线查询/报告

Question

这可能需要一些解释，但我试图弄清楚这是否是一个我还不知道如何编写的查询，或者通过改变我构建的MS Access数据库的当前结构来更好地解决这个问题。所以这么说..。

我有一个MS Access数据库，其中有三个主要表用于此问题：

[Products]
[ProductChanges]
[Managers]

它们之间有这样的联系：

[Products].[ID] -> [ProductChanges].[PName]
[Products].[CurrManager] -> [Managers].[ID]
[ProductChanges].[CurrManager] -> [Managers].[ID]

Products表列出了我们服务的所有当前、过去和未来产品(或将服务)，ProductChanges表包含对该产品的所有更改(经理之间的移动、状态更新等)。我已经让它从导航视图中看起来非常漂亮，每个产品的表单都显示了它的当前状态、管理器和子表单，其中一个子表单显示了它过去和将来的更改(来自ProductChanges表)。

我还编写了一个update查询，它在每次打开数据库时运行，该查询根据ProductChanges表中更改的日期更新产品表中的CurrManager字段，并将其更新为ProductChanges表中的CurrManager字段的值。也许我应该改变其中一个字段名，这听起来很混乱，因为它是打印出来的。

这里有一个棘手的部分:随着新产品的到来，我们需要知道哪些经理可以承担更多(他们有一个最大的能力)。当前的容量非常简单，可以查询Products表的字段“CurrManager”，并计算当前分配给它们的产品的ID，但是我们需要查看将来谁将拥有容量，最好是时间线格式。由于产品经常会换手(有些完成后会转移到其他地方)，所以当数据库根据ProductChanges表中的日期进行更新时，每个经理正在监督的产品数量将发生变化。

因此，理想情况下，最终输出的示例如下：

          1/1/2016 | 1/8/2016 | 1/15/2016
Smith, J  10         12         9

这取决于有多少产品计划分配给J. Smith，或者在这段时间内从J. Smith转移。本质上是“按周计算的容量”。我已经取得了一些进展，但已经被挂了一段时间。这个查询将为我提供一个基本的历史时间线，但我似乎无法用它来总结日期范围的组合：

SELECT Managers.LastName
    ,Managers.FirstName
    ,ProductChanges.Effectivedate
    ,[ProductChanges].[EffectiveDate] - Weekday([ProductChanges].[EffectiveDate]) + 1 AS [Week Of]
    ,Count(Products.Name) AS NumProducts
FROM Managers
RIGHT JOIN (
    Products INNER JOIN (
        ProductChanges LEFT JOIN Managers AS Managers_1 ON ProductChanges.CurrManager = Managers_1.ID
        ) ON Products.ID = ProductChanges.InnCode
    ) ON Managers.ID = Products.CurrManager
    ,Managers.FirstName
    ,ProductChanges.Effectivedate
    ,[ProductChanges].[EffectiveDate] - Weekday([ProductChanges].[EffectiveDate]) + 1
HAVING (
        ((Managers.LastName) IS NOT NULL)
        AND ((ProductChanges.Effectivedate) > 0)
        )
ORDER BY Managers.LastName;

这是一个混乱，我知道，所以，如果(和什么时候)需要澄清，我将密切关注这一个。

Answer 1

我有个主意!它最终是一个2步的过程，以得到我需要它做的事情，但只要记录是最新的产品变化，这就显示了一个经理获得和丢失产品的实际时间表。

我不得不这样做的主要原因是，用户有能力更改产品的过去历史--在缺少一些东西的情况下，这意味着ProductChanges表中的记录ID基本上可以被认为是相对于EffectiveDate字段来说是随机的，对于产品更改历史已被更改的经理来说，对ProductChanges表进行运行的总查询几乎是不可行的。我编写了一个快速脚本来专门构建一个表，用于按我所需的方式排序ID：

Dim db As Database Set db = CurrentDB db.Execute "DROP TABLE GainLoss;" db.Execute "CREATE TABLE GainLoss(AutoID AutoIncrement, HCID Long, MgrID Long, LastName varchar(100), FirstName varchar(100), HCDate Date, GainLoss Long)" db.Execute "INSERT INTO GainLoss(HCID, MgrID, LastName, FirstName, HCDate, GainLoss) SELECT hc.ID, s.ID, s.LastName, s.FirstName, hc.EffectiveDate, iif(s.id = hc.currmanager,1,-1) FROM Managers s INNER JOIN ProductChanges hc ON (s.ID = hc.currmanager OR s.ID = hc.prevmanager) ORDER BY LastName, FirstName, EffectiveDate;"

这也给了我一个加一个，一个经理得到的每一个产品，和一个负一个，他们传递的每一个。通过这个查询将其抛出：

SELECT A.AutoID, A.MgrID, A.LastName, A.FirstName, A.HCDate, Sum(B.GainLoss) AS Capacity FROM GainLoss AS A LEFT JOIN GainLoss AS B ON (A.AutoID >= B.AutoID) AND (A.MgrID = B.MgrID) AND (A.HCDate >= B.HCDate) GROUP BY A.LastName, A.FirstName, A.MgrID, A.HCDate, A.AutoID;

你有一个时间表，显示每个变化，无论是添加或减去一个产品到经理的投资组合。

Answer 2

考虑一个交叉表查询 (其他RDMS中不可用的唯一Max查询)，它将数据沿值列(即生效日期)进行枢轴，运行任何聚合(计数、求和、Avg、Min、Max等)。

下面在聚合查询中将所有三个管理器、ProductChanges和Products表连接起来。根据需要根据您的架构进行调整。在MSAccess.exe中，您可以在“设计视图”中调整交叉表，选择行标题(一个或多个)、列标题(只有一个)和值(只有一个)字段：

TRANSFORM Count(p.InnCode) AS NumProducts
SELECT m.LastName, m.FirstName
FROM (Managers m
INNER JOIN ProductChanges pc
    ON m.[ID] = pc.[CurrManager])
INNER JOIN Products p
    ON pc.[PName] = p.[ID]
GROUP BY m.LastName, m.FirstName
PIVOT pc.Effectivedate

请注意: MS访问表/查询有255列的限制。因此，如果遇到254个以上的唯一日期，则需要筛选可以使用PIVOT Column IN()子句处理的日期。考虑通过VBA参数来动态地传递未来3-4周的电流。

TRANSFORM Count(p.InnCode) AS NumProducts
SELECT m.LastName, m.FirstName
FROM (Managers m
INNER JOIN ProductChanges pc
    ON m.[ID] = pc.[CurrManager])
INNER JOIN Products p
    ON pc.[PName] = p.[ID]
GROUP BY m.LastName, m.FirstName
PIVOT pc.Effectivedate IN ('1/1/2016', '1/8/2016', '1/15/2016')

Answer 3

最简单的方法是查询您的表，并添加一个字段，该字段将日期转换为与您所做的类似的“周”。

ProductChanges.EffectiveDate - Weekday(ProductChanges.EffectiveDate) + 1

从那时起，这个字段只是一个简单的查询分组--在您将需要的所有其他表加入到ProductChanges表之后。