PERCENTILE CALCULATION

[ARW17]

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?

 

[xenou]

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.