Calculated field based on calculated field

[paultje_bos]

I have a general Access question, is it correct that Access (2007) doesn't store all decimals of a number in calculated field? For example, if I divide 10 by 7, the outcome would be 1.4285714..., but if I round the number in the calculated field with 2 decimals I would get the result 1.43. But when I want to use this result for a different calculated field, it would use 1.43 and not 1.4285714..., which gives me in the end a small difference.

Basically what I ask is, can't Access do the same as Excel if I talk about results of formulas?

 

[jorismoerings]

Hi,

Access stores exactly what you tell it to store. So if you have a calculated field with rounding applied; it will store the rounded data;

Calculated columns are part of life on a spreadsheet, but do not belong in a database table.
Never store a value that is dependent on other fields: it's a basic rule of normalization.
Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.


Ask yourself this question, "Would it be wrong or will it hunt me later if my field contained a value different from the calculation?"
1. If you answer this question with Yes, you must not store the field but create it with every use and format it to your needs.
2. If the difference would be meaningful, you do need the field but than would need to keep the restrictions of a calculated field in mind.

 

[paultje_bos]

Hi Joris, thanx for your comment, it is very valuable. I don't really "store" it, it is more that I have a macro that first calculates date from a table, and then I have a second query that calculates something different with the outcomes from the first query. So to value is not stored, but refreshed every time I run the query.
But what you say about the Round function is good to know and also your comment that Access does exactly what I want to do it. Thank you!

 

[jorismoerings]

Hi,

Glad i could help.

My basic rules in creating tables and querys is to never use any format related functions in calculating fields.
Formatting is just what it says it is "formatting" hence it's just help to improve the visual look of things; so better to format only in forms or reports when you actually need the visual look.

Avoiding the confusion is better than to explain and solve afterwards.