PERCENTILE CALCULATION

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
107
Need to find the 50-80th percentile of duration per person per worktype. I figured out how to rank/put them in order and thought I could then separately sum the number of workloads per person per worktype and again separately find the percentile and finally get to the population I'm looking for. I used this as the first step:

INSERT INTO PERCENTILES ( firstofactor, worktype, dur, PERCENTILE )
SELECT [4 - Export to Excel].firstofactor, [4 - Export to Excel].worktype, [4 - Export to Excel].dur, ((select count([firstofactor]) as HowMany
from [4 - Export to Excel] as DUPE
where dupe.dur < [4 - Export to Excel].dur and dupe.firstofactor = [4 - Export to Excel].firstofactor and dupe.worktype = [4 - Export to Excel].worktype)+1) AS BeatenBy
FROM [4 - Export to Excel]
ORDER BY [4 - Export to Excel].firstofactor, [4 - Export to Excel].worktype, [4 - Export to Excel].dur;

It runs as a SELECT in about 15 seconds, but I can't get it to make a table or append. I thought that was my best bet at adding the additional steps. Does anyone have a straightforward way to do this? Should I just export it to Excel, run the calculations there, and then upload back to Access?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Hi, there is nothing particular in your query that seems to be a problem. It seems most likely that due to the criteria/condition in your WHERE clause means there are no records to insert.
 

Forum statistics

Threads
1,144,523
Messages
5,724,836
Members
422,582
Latest member
Vaibhav Wadhwa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top