Sum of calculated field returns wrong result in MS Access query?

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.

开发者_JAVA百科

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)

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.

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).

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?

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.

---

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.

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

(SELECT DISTINCT Type,Rate FROM t_rate)

but Access will convert it to

[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.