Precision vs. Iteration

[milkman2500]

Hi,

In Excel there is a common issue with precision vs. iteration when carrying calculations through multiple formulas. Excel has an option to adjust for this. I'm experiencing the same issue between Access' queries and reports.

I have a query which calculates the percent increase between two columns on each record. I have a 2nd query which averages these differences.

I have a report which with an unbound control that calculates the difference between the totals (rather than first individuals then averaging up).

The percent differences on the query is different than the report by 1% due to rounding.

Is there a way to adjust the iteration vs. precision in Access 2010 similar to Excel 2010?

Thanks.

 

[CJ_London]

If you are using Round or Currency you will limit the precision by the number of dp's (Currency is 4, Round is as specified) So recommend you do not use Currency and set the dp's for round for as large are you can - at least until the final value

 

[milkman2500]

Thanks for the reply. However, the issue isn't a single calculation. It's when you calculate through multiple formulas and the program calculates the figures so exactly that it provides a different answer then you would get if you did it paper/pencil.

The explanation for excel is here, however I'm interested in knowing if there is a similar option/method for Access 2010.

http://office.microsoft.com/en-ca/excel-help/set-rounding-precision-HA010218870.aspx

 

[CJ_London]

if there is a similar option/method for Access 2010

Similar - round has already been mentioned which affects the underlying value - same as Excel.

The equivalent of the dialog box in excel is Format so you can format each column to say 2dp by entering 0.00 in the format box - you can see the same thing in excel if you choose the custom option for numbers.

I stand by my original post.

You might find this link useful useful about rounding - examples relate to Excel and Access among others http://support.microsoft.com/kb/196652