Databases
StackOverflow2013
Postsdbo
POSum of calculated field returns wrong result in MS Access query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSum of calculated field returns wrong result in MS Access query?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USMOLAP
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSum of calculated field returns wrong result in MS Access query?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSum of calculated field returns wrong result in MS Access query?
      1. USMOLAP
      1. VTFavorite
    1. COHow do you know what rate is for which customer? If you go by the `Type` column, all 3 rates are joined with every row in the Customer table, therefor you get 180 (3*60)
      singulars
      1. POSum of calculated field returns wrong result in MS Access query?
      1. USJacob
    2. COIs there a third table describing the relationship between ReportCountry and Area? That is the key to the query you're trying to write
      singulars
      1. POSum of calculated field returns wrong result in MS Access query?
      1. UShawbsl
    3. COI don't know the rate for each customer. I agree that it's poor table design of table2. But just think of table2 as some kind of mastertable that could hold even more static data not tied to the single customer, but tied to Type or ReportCountry.
      singulars
      1. POSum of calculated field returns wrong result in MS Access query?
      1. USMOLAP
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload