title: Sum of calculated field returns wrong result in MS Access query?
body: <p>I have these 2 tables:</p>
  
  <p><strong>Table1</strong>:</p>
  
  <pre><code>CustomerID Area     Type                   Revenue
  1          Europe   Institutional Clients   10
  2          Asia     Institutional Clients   10
  3          USA      Institutional Clients   10
  </code></pre>
  
  <p><br></p>
  
  <p><strong>Table2</strong>:</p>
  
  <pre><code>Report Country       Type                   Rate
  DK                 Institutional Clients    2
  SE                 Institutional Clients    2
  FI                 Institutional Clients    2
  </code></pre>
  
  <p>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.</p>
  
  <p><BR>
  <BR>
  <BR>
  <BR></p>
  
  <p><strong>Query version1</strong>:
  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)</p>
  
  <pre><code>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];
  </code></pre>
  
  <p>Returns:</p>
  
  <pre><code>CustomerID   Area   Type                  CalculatedField
  1            Europe Institutional Clients   20
  2            Asia   Institutional Clients   20
  3            USA    Institutional Clients   20
  </code></pre>
  
  <p><BR></p>
  
  <p><strong>Query version1B</strong>:  I remove the fields CustomerID and Area.</p>
  
  <pre><code>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]);
  </code></pre>
  
  <p>Returns:</p>
  
  <pre><code>Type                 CalculatedField
  Institutional Clients   20
  </code></pre>
  
  <p><BR>
  <BR>
  <BR></p>
  
  <p><strong>Query version2</strong>:
  Here I add SUM of the Calculated field.
  This query returns 180 (which is wrong).</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Returns:</p>
  
  <pre><code>Type                   CalculatedField
  Institutional Clients   180
  </code></pre>
  
  <p><BR>
  <BR></p>
  
  <p>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?</p>
  
  <p>I know I could just make a new query on top of <strong>Query version1</strong> that makes the correct sum. But I would like to avoid having 2 queries for this purpose.</p>