Calculations Rounding off to the whole

[StephenB]

I have five fields on a form called [A], , [c], [d] and [AVG]. I would like [AVG] to be the average of the four other fields. I using "[AVG]=[A]++[c]+[d]/4". However, that's rounding the answer up to whole numbers even though I have both the table and form fields set to "Decimal: 2" on properties. [1],[1],[1] and [4] are returning [2] when it should be returning [1.75]. I read a great deal about the calculations problems on Access, but I know you can write code to round off to a certain level.

Can anyone help me out with something to round off to the hundreth? I'd greatly appreciate it.

TIA,
Stephen

 

[Elana]

Hi -

I just did a quick test on a form and I'm not having the rounding up problem that you're having.

For the field named AVG, what is your control source?

 

[chrismcbride]

Are you perhaps putting the value of the calculation into a integer variable or control that is bound to a field with type Long or Integer???
Chris

 

[Jack Cowley]

See the article below for rounding to the requested number of decimal places....

http://support.microsoft.com/support/kb/articles/Q97/5/24.ASP?LN=EN-US&SD=gn&FR=0&qry=q97524&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97

 

[StephenB]

Elana: The name of the control source is [AVG]. That's the name of the field on the table as well.

Chris: Thanks! I looked up fieldsize properties on Microsoft help and changed the field size to "Single". I'm getting the correct returns.

Jack: The Microsoft article was very good. It gave me a heads up on some other things I was working on. You saved me some work on other dbs I'm working on. I appreciate you replying.

Again, thank you all.

 

[Elana]

Hi - glad you got it figured out with sgl data type.

FYI - if "AVG" is a calculation, it really shouldn't be a field in a table.

Generally speaking, you should follow the rules of database normalization, and not store calculated values in tables, unless you are trying to capture a snapshot of information at a particular time.

EB

 

[StephenB]

Elana;
Thanks for the tip. Is "Database Normalization" and in depth topic or is there somewhere you can recommend I look for information. As you can probably tell, I've learn Access piecemeal and wouldn't mind learning more about the topic.

I'm also interested in why it's not a good idea to store calculated values in a table. If you can comment on that I'd appreciate it also.
Thanks,
Stephen

 

[Fornatian]

Storing calculated values adds extra bulk to your database when its not essential - you have all the elements required to recreate the answer to the calculation, and in most instances it is beneficial to do it this way in case any of the base data has changed since the last calculation. Using queries anbd controls for calculations is much more efficient as they are not stored and the results exist only for the life of the form or report.

There are a few instances where you might need to store the value, i.e.previous years invoices with a changing per unit price scale but that is usually worked around by using date specific pricing tables.

Ian