Quintile

[aat]

Hi everyone!

i hope this one is easy. I would like to run a query after i have updated a record, that returns the quintile ("sixtile" actually but if you show me quintile i suppose i can easily vary it) of my record based on one particular field, across the database. So, say the population for that field varies between 0% to 100%, if my record has a % of 15%, it should return 1 as first quintile.
On excel i use the following formula, where "factor" is my field, and min / max are relating to the population for that field: =IF(ROUNDUP(([@Factor]-MIN([Factor]))/((MAX([Factor])-MIN([Factor]))/6),0)=0,1,ROUNDUP(([@Factor]-MIN([Factor]))/((MAX([Factor])-MIN([Factor]))/6),0)).


(which is relevant for "sixtiles").

I have not started to draft anything, but at first sight, the min / max reference are going to be a problem. Any suggestion welcome!

Aat

[June7]

Here are some threads discussing quartiles, maybe will help:

http://www.access-programmers.com/ma...-concepts.aspx

https://www.accessforums.net/queries...light=quartile

https://www.accessforums.net/access/...light=quartile