计算字段的总和在MS Access查询中返回错误的结果？

I have these 2 tables:

我有这两个表:

Table1:

CustomerID Area     Type                   Revenue
1          Europe   Institutional Clients   10
2          Asia     Institutional Clients   10
3          USA      Institutional Clients   10

Table2:

Report Country       Type                   Rate
DK                 Institutional Clients    2
SE                 Institutional Clients    2
FI                 Institutional Clients    2

I want to make a query that joins the two tables and make a calculated field (Revenue*Rate). But when I use the MS Access query designer the sum of calculated field returns the wrong result.

我想创建一个连接两个表并创建一个计算字段(Revenue * Rate)的查询。但是当我使用MS Access查询设计器时,计算字段的总和会返回错误的结果。

Query version1: This query returns 20 per customer (which is correct) and 60 in total, but the fields are not grouped into 1 row. (if I remove the fields CustomerID and Area I get 1 row, but result says 20?! Se version1B below)

查询版本1:此查询每个客户返回20个(这是正确的),总共60个,但这些字段未分组为1行。 (如果我删除字段CustomerID和Area I获得1行,但结果显示20?!Se version1B如下)

SELECT t_Customer.CustomerID, t_Customer.Area, t_Customer.Type, [Revenue]*[Rate] AS CalculatedField
FROM t_Customer INNER JOIN t_Rate ON t_Customer.Type = t_Rate.Type
GROUP BY t_Customer.CustomerID, t_Customer.Area, t_Customer.Type, [Revenue]*[Rate];

Returns:

CustomerID   Area   Type                  CalculatedField
1            Europe Institutional Clients   20
2            Asia   Institutional Clients   20
3            USA    Institutional Clients   20

Query version1B: I remove the fields CustomerID and Area.

查询版本1B:我删除了CustomerID和Area字段。

SELECT t_Customer.Type, ([Revenue]*[Rate]) AS CalculatedField
FROM t_Customer INNER JOIN t_Rate ON t_Customer.Type = t_Rate.Type
GROUP BY t_Customer.Type, ([Revenue]*[Rate]);

Returns:

Type                 CalculatedField
Institutional Clients   20

Query version2: Here I add SUM of the Calculated field. This query returns 180 (which is wrong).

查询版本2:这里我添加了计算字段的SUM。此查询返回180(这是错误的)。

SELECT t_Customer.Type, Sum(([Revenue]*[Rate])) AS CalculatedField
FROM t_Customer INNER JOIN t_Rate ON t_Customer.Type = t_Rate.Type
GROUP BY t_Customer.Type;

Returns:

Type                   CalculatedField
Institutional Clients   180

Is there a way to use the MS Access query designer to display the correct Sum of the calculated field, so I can have only 1 query for this purpose?

有没有办法使用MS Access查询设计器显示计算字段的正确总和,所以我只能有1个查询用于此目的?

I know I could just make a new query on top of Query version1 that makes the correct sum. But I would like to avoid having 2 queries for this purpose.

我知道我可以在查询版本1之上创建一个新查询,以获得正确的总和。但我想避免为此目的进行2次查询。

1 个解决方案

#1

SELECT t_Customer.CustomerID,
       t_Customer.Area, 
       t_Customer.Type, 
       [Revenue] * [Rate] AS CalculatedField
  FROM t_Customer 
  JOIN (SELECT DISTINCT Type, Rate 
         FROM t_rate) t_rate ON t_Customer.Type = t_Rate.Type

If you want it all one row then:

如果你想要一行,那么:

  SELECT t_Customer.Type, 
         SUM([Revenue] * [Rate]) AS CalculatedField
    FROM t_Customer 
    JOIN (SELECT DISTINCT Type, Rate 
            FROM t_rate) t_rate ON t_Customer.Type = t_Rate.Type
GROUP BY t_Customer.Type

Returns:

Type                    CalculatedField
Institutional Clients   60

Note that this change cannot be made with Access Query Designer (in Design Mode) you have to switch to SQL View.

请注意,无法使用Access Query Designer(在设计模式下)进行此更改,您必须切换到SQL View。

Also note that the SELECT DISTINCT part can be typed both within parentheses like this

另请注意,SELECT DISTINCT部分可以在这样的括号内输入

(SELECT DISTINCT Type,Rate FROM t_rate)

but Access will convert it to

但Access会将其转换为

[SELECT DISTINCT Type,Rate FROM t_rate]. 

when you save and edit the query again. It produces the same result though. So it works just fine.

当您再次保存和编辑查询时。它产生相同的结果。所以它运作得很好。