Need a function to result in Percent in update query

[kpal29]

I have a field named PERCENT. This field is the result of the following function in the Update To part of the query:
[MARGIN]/[TOTALREVENUE]

No matter what format I give the field in the table and/or the query field I cannot get this to come out as the correct percent?? I keep getting 1's and zeros

Please help.

 

[EBGreen]

What types are [MARGIN] and {TOTALREVENUE]?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[lespaul]

and as an aside, storing a calculated value breaks third normal form. Why are you interested in storing this value?

For more information on normalization see 'The Fundamentals of Relational Database Design'



Leslie

 

[kpal29]

Margin and TotalRevenue are number formats, TotalRevenue is double and Margin is long integer. The field Percent is a field in my table along with Margin and Percent.

I need to store this percentage in order to categorize my records by

1) Files with Margin Percent between 1-49
2) Files with Margin Percent > 50
3) Files with zero or negative margin

 

[lespaul]

again why would you store this instead of creating it in a query on the fly? What happens if there is an error and TotalRevenue is incorrect, someone changes TotalRevenue and now you have to update this field again. Or the Margin is adjusted and now you have to update your percent field AGAIN. That is one of the reasons that you should always calculate in a query and not store calculated values (there are exceptions to this rule).

Anytime you need to display this percentage, run the query that calculates it.

Again I would recommend reading the 'Fundamentals' link I posted above. You will find that if you normalize your tables now, you will have a lot fewer problems in the future extracting the data you need.


Leslie