Search code examples
ms-accesssumint

Access 2010 Sum wrong result

This is my issue.

First step. I sum the column HH (alias SUM_Original_values) and I get 419. This result is correct. (see the pic below)

enter image description here

Second step. I want take only INT values of the HH column's, and I get 417. This result is correct. (see the pic below)

enter image description here

Third step. I want to create a column Global_Int_Sum_HH (416), but this value is different from Int_Sum_HH (417)

enter image description here

Why the results are differents ?

This is the query 

SELECT 
 Year, 
 Month, 
 Customer,
 User, 
 Int(Sum(HH)) AS Int_Sum_HH, 

(
SELECT  (int(sum(int(HH)))) AS Global_Int_Sum_HH
FROM T_Att
HAVING (((Year)="2016") AND ((month)="03") AND ((Customer)="FC"));
) AS Global_Int_Sum_HH, 

Customer + Str(Global_Int_Sum_HH) AS [KEY]

FROM T_Att

GROUP BY Year, Month, Customer, User
HAVING (((Year)="2016") AND ((Month)="03") AND ((Customer)="FC"));
Solution

  • It looks to me like there's an inconsistency in your order of operations.

    In one instance you int the sum, and in the second instance you sum the int.

    SELECT 
 Year, 
 Month, 
 Customer,
 User, 
 Sum(Int(HH)) AS Int_Sum_HH, 
-- ^ changed order of events to match sub-query
(
-- v removed redundant int()
SELECT  sum(int(HH)) AS Global_Int_Sum_HH
FROM T_Att
HAVING (((Year)="2016") AND ((month)="03") AND ((Customer)="FC"));
) AS Global_Int_Sum_HH, 

Customer + Str(Global_Int_Sum_HH) AS [KEY]

FROM T_Att

GROUP BY Year, Month, Customer, User
HAVING (((Year)="2016") AND ((Month)="03") AND ((Customer)="FC"));

    The above adjustment will make the "right" answer = 416 for both values. If you were to change your order of operations to both be Int(Sum(HH)), then the Global_Int_Sum_HH value would equal 419 and your Int_Sum_HH column would be 417 instead.