Access 2010 - 使用Union All和Sum Iif查询以生成多个表中特定事件的总计数/总和

I have a database with four tables: Table 1, 2, 3, & 4. Each table holds the following fields: ID (AutoNumber), Date (Date/Time), Name (Text), and Item (Text)

我有一个包含四个表的数据库:表1,2,3和4.每个表包含以下字段:ID(自动编号),日期(日期/时间),名称(文本)和项目(文本)

I created an SQL Query that uses Union All and a Sum Iif expression to retrieve the number of "Apples", "Oranges", and "Pears" that occur under the Item column for each individual. For example, when the Query runs, I want the query to provide me the date, the name of the individual, and the grand total of "Apples", "Oranges", and/or "Pears" for the date and individual. (12/15/2015 - Dee Wolf - 15 Apples, 10 Oranges, 20 Pears)

我创建了一个SQL查询,它使用Union All和Sum Iif表达式来检索每个人的Item列下出现的“Apples”,“Oranges”和“Pears”的数量。例如,当Query运行时,我希望查询向我提供日期,个人姓名以及日期和个人的“苹果”,“橘子”和/或“梨”的总计。 (12/15/2015 - Dee Wolf - 15个苹果,10个橘子,20个梨)

Problem: My query successfully counts all of the data from all 4 tables, however, it is not producing a total. It is, instead, giving my a 1-to-1 count for each table. For example: Table 1 will show that John has 1 apple and 1 orange, where Table 2 shows that John has 2 apples and 1 orange. When I run my query, it will show that John has 2 apples, 1 apple, 1 orange, and 1 orange (a result for each table), but I want it to just say John has 3 total apples.

问题:我的查询成功计算了所有4个表中的所有数据,但是,它没有产生总数。相反,它为每张桌子提供一对一的计数。例如:表1将显示John有1个苹果和1个橙色,其中表2显示John有2个苹果和1个橙色。当我运行我的查询时,它会显示约翰有2个苹果,1个苹果,1个橙色和1个橙色(每个表的结果),但我想要它只是说约翰总共有3个苹果。

I have written this code in another database and it seems to produce the results I want. But I'm not sure why I'm unable to reproduce the results. Am I overlooking something?

我已将此代码写入另一个数据库,它似乎产生了我想要的结果。但我不确定为什么我无法重现结果。我忽略了什么吗?

Here is the SQL:

这是SQL:

SELECT Table1.Date, Table1.Name, Sum(IIf([Table1]![Item]="Apples",1,Null)) AS CountApples, Sum(IIf([Table1]![Item]="Oranges",1,Null)) AS CountOranges, Sum(IIf([Table1]![Item]="Pears",1,Null)) AS CountPears
FROM Table1
GROUP BY Table1.Date, Table1.Name
HAVING (((Table1.Date) Between [SS/SS/SS] And [EE/EE/EE]) AND ((Table1.Name)))
UNION ALL
SELECT Table2.Date, Table2.Name, Sum(IIf([Table2]![Item]="Apples",1,Null)) AS CountApples, Sum(IIf([Table2]![Item]="Oranges",1,Null)) AS CountOranges, Sum(IIf([Table2]![Item]="Pears",1,Null)) AS CountPears
FROM Table2
GROUP BY Table2.Date, Table2.Name
HAVING (((Table2.Date) Between [SS/SS/SS] And [EE/EE/EE]) AND ((Table2.Name)))
UNION ALL
SELECT Table3.Date, Table3.Name, Sum(IIf([Table3]![Item]="Apples",1,Null)) AS CountApples, Sum(IIf([Table3]![Item]="Oranges",1,Null)) AS CountOranges, Sum(IIf([Table3]![Item]="Pears",1,Null)) AS CountPears
FROM Table3
GROUP BY Table3.Date, Table3.Name
HAVING (((Table3.Date) Between [SS/SS/SS] And [EE/EE/EE]) AND ((Table3.Name)))
UNION ALL
SELECT Table4.Date, Table4.Name, Sum(IIf([Table4]![Item]="Apples",1,Null)) AS CountApples, Sum(IIf([Table4]![Item]="Oranges",1,Null)) AS CountOranges, Sum(IIf([Table4]![Item]="Pears",1,Null)) AS CountPears
FROM Table4
GROUP BY Table4.Date, Table4.Name
HAVING (((Table4.Date) Between [SS/SS/SS] And [EE/EE/EE]) AND ((Table4.Name)));

1 个解决方案

#1

HAVING ((...) AND ((Table1.Name)))

still doesn't make much sense...

仍然没有多大意义......

To get the totals, wrap your whole query into a Sum() / GROUP BY query:

要获取总计,请将整个查询包装到Sum()/ GROUP BY查询中:

SELECT [Date], [Name], Sum(CountApples) AS TotalApples, Sum(CountOranges) AS TotalOranges, 
                       Sum(CountPears) AS TotalPears
FROM (
    -- your UNION query goes here
)
GROUP BY [Date], [Name]

(Shamelessly lifted from https://*.com/a/32274957/3820271)

(无耻地解除了https://*.com/a/32274957/3820271)

#1

HAVING ((...) AND ((Table1.Name)))