none
Strange Access Calculation Issue (SUM with Division) RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Dear All,

    I have a table T_COPA which has a field called NY_TGT with a sum of 537777667. 

    I created a calcuated field with formula NY_TGT*1,37/1000000.  I expect it to return 736,75, but it returns 686,6.

    To verify this issue, I created the following query with several calculted fields using different denominators.  You can see that the fields with division calcuation are all wrong. The bigger the denominator is, the bigger the error is.

    Any help or hint would be highly appreciated.

     

    SELECT 

    Sum([NY_TGT]) AS NY0,

    Sum(([NY_TGT]*1.37)) AS NY1,

    Sum(([NY_TGT]*1.37)/1000) AS NY2,

    Sum(([NY_TGT]*1.37)/10000) AS NY3,

    Sum(([NY_TGT]*1.37)/100000) AS NY4,

    Sum(([NY_TGT]*1.37)/1000000) AS NY5
    FROM T_COPA

    It returns the following value

    NY0 537777667,0
    NY1 736755403,8
    NY2 736709,4
    NY3 73627,3
    NY4 7314,8
    NY5 686,6

    Bing

    Samstag, 4. Dezember 2010 00:52
    |

Antworten

  • Question
    Anmelden
    0
    Anmelden

    I found the root cause which is related to DATA TYPE.

    The NY_TGT field in my table has Data TYPE LONG INTEGER, so it has no decimal place  - this is actually desired.

    For the calculated field SELECT [NY_TGT]*1.37)/1000000, Access automatically assigns Data Type DECIMAL with decimal place=2. This causes the data beyond 2 decimal place getting lost.  If the table has 20,000 records, and by average each record  loses 0,0025, then the SUM will be 50 less.

    To resolve the problem, I changed the Data Type of NY_GT from INTEGER to DOUBLE. Then the calculated field has also data type DOUBLE by default.

    Cheers

     

    • Als Antwort markiert bp0169 Samstag, 4. Dezember 2010 13:25
    Samstag, 4. Dezember 2010 08:07
    |