Deciling in Access

[vishal09]

Hi,



I have a dataset with around 3.1 Million records with Sales information of different Physicians. I want to calculate decile of Physicians based on their Sales data. Is there any way to calculate decile in Access.
Also keeping in mind that the recordset is huge. Thanks in advance!

[DubCap01]

Hi Vishal09

try taking a subset ( maybe one millionth of your records lol) and create another database to try your theory?

[CJ_London]

you'll need a query for each decile

the principle would be to

get your first decile

SELECT Top 10% *
FROM tblSales
ORDER BY SalesValue

to get the second

SELECT Top 20% *
FROM tblSales LEFT JOIN (SELECT Top 10% * FROM tblSales ORDER BY SalesValue) AS P ON tblSales.ID=P.ID
WHERE P.ID is null


to get the third

SELECT Top 30% *
FROM tblSales LEFT JOIN (SELECT Top 20% * FROM tblSales ORDER BY SalesValue) AS P ON tblSales.ID=P.ID
WHERE P.ID is null

etc

to get the last

SELECT *
FROM tblSales LEFT JOIN (SELECT Top 90% * FROM tblSales ORDER BY SalesValue) AS P ON tblSales.ID=P.ID
WHERE P.ID is null

ID needs to be a field with a value unique to each row

As DubCap says, suggest take a small set of data to test the principle. Also ensure your data is properly indexed, it will make a huge difference to the speed the query runs. In the above example you would index ID and SalesValue

[June7]

There have been several threads on quintiles/quartiles/percentiles. Search forum on those keywords. Further filter with my username. Should get at least 4. Here is one https://www.accessforums.net/access/...74/index3.html