生成包含多个表/查询的MS Access报告

Question

我正在使用Access 2007从一个部门的多个单位导入统计数据(Excel)。尽管它们共享相似的字段，但每个Unit都有自己的表，因为它们也有需要跟踪的特定于unit的数据/字段。

表:UnitA-(字段:日期、汽车、船只、标签、枪支、失踪人员等)

表:单位B-(字段:日期、汽车、船、标签、枪、板凳搜查证等)

表:单位C-(字段:日期、汽车、船、标签、枪、指纹等)

我需要创建一个摘要报告，该报告查询开始日期和结束日期，然后生成一个将显示以下内容的报告：

每个记录中每个单独字段的总和，即每个记录中每个共享字段的总和，即总和(UnitA！Cars)+总和(UnitB！Cars)，等等。

我可以为每个单元创建一个报告，没有任何问题。

当我放弃三个单元报告并尝试使用子报告时，我的问题就开始了。Access将提示我输入新的开始日期和结束日期。

有人有更好的解决方案吗？我是否应该创建一个包含开始日期和结束日期字段的“提示”窗体，以便从每个查询中访问它们？

Answer 1

您可以使用联合创建单个查询

SELECT Date, Cars, Boats, Tags, Guns,
   [Missing Persons], Null AS [Bench Warrants], Null AS Fingerprints FROM UnitA
UNION ALL
SELECT Date, Cars, Boats, Tags, Guns,
   Null,              [Bench Warrants],         Null                 FROM UnitB
UNION ALL
SELECTG Date, Cars, Boats, Tags, Guns,
   Null,              Null,                     Fingerprints         FROM UnitC

每次选择中必须有相同数量的字段。第一个SELECT的字段名将用于结果。

但是，我建议使用另一种DB设计。将所有常用字段放在一个公用表中，并将特定于设备的字段放在以一对一关系连接的其他表中

CREATE TABLE Unit (
    UnitID  CONSTRAINT PK_unit PRIMARY KEY,
    Date datetime,
    Cars long,
    Boats long,
    Tags long,
    Guns long
)

CREATE TABLE UnitA (
    UnitID  CONSTRAINT PK_unitA PRIMARY KEY,
    [Missing Persons] Long
)
ALTER TABLE UnitA
   ADD CONSTRAINT FK_unitA_unit
   FOREIGN KEY (UnitID) REFERENCES Unit (UnitID)


CREATE TABLE UnitB (
    UnitID  CONSTRAINT PK_unitB PRIMARY KEY,
    [Bench Warrants] Long
)
ALTER TABLE UnitB
   ADD CONSTRAINT FK_unitB_unit
   FOREIGN KEY (UnitID) REFERENCES Unit (UnitID)


CREATE TABLE UnitC (
    UnitID  CONSTRAINT PK_unitC PRIMARY KEY,
    Fingerprints Long
)
ALTER TABLE UnitC
   ADD CONSTRAINT FK_unitC_unit
   FOREIGN KEY (UnitID) REFERENCES Unit (UnitID)

现在，您可以使用左连接创建一个查询。

更简单的设计是只创建一个表，并允许空字段

CREATE TABLE Unit (
    UnitID  CONSTRAINT PK_unit PRIMARY KEY,
    Date datetime,
    Cars long,
    Boats long,
    Tags long,
    Guns long,
    [Missing Persons] Long,
    [Bench Warrants] Long,
    Fingerprints Long
)

与直接导入excel表格不同，只需链接excel表格，然后使用自定义的追加查询将其内容附加到公共表格。

Answer 2

更好的做法是将共享单位数据导入到单位表中，然后将统计数据导入到明细表中：

CREATE TABLE Unit (
    UnitID CONSTRAINT PK_unit PRIMARY KEY,
    UnitNo VarChar,
    OtherUnitInfo )

CREATE TABLE DetailCategory (
    CategoryID CONSTRAINT PK_DetailCategory PRIMARY KEY,
    Category Varchar ) -- example rows: 'Guns' 'Missing Persons' etc

CREATE TABLE Unit_DetailCategory (
    UnitID FOREIGN KEY (UnitID) REFERENCES Unit (UnitID),
    CategoryID FOREIGN KEY (CategoryID REFERENCES DetailCategory (CategoryID),
    Qty Long )

然后，您可以使用TRANSFORM/PIVOT来获取求和的值：

TRANSFORM Sum(Unit_DetailCategory.Qty) AS SumOfQty
SELECT 'Sum of Unit Values' AS FunctionName
FROM DetailCategory 
RIGHT JOIN (Unit INNER JOIN Unit_DetailCategory 
    ON Unit.UnitID = Unit_DetailCategory.UnitID) 
    ON DetailCategory.CategoryID = Unit_DetailCategory.CategoryID
GROUP BY 'Sum of Unit Values'
PIVOT DetailCategory.Category

注意-我的语法在CREATE语句中可能有缺陷，您可能必须使用转换/透视来精确地获得您想要的东西。但这在我的机器上是有效的。