I need some BIG help here. I have a report that lists a series of procedures, attempts and successes, and elapsed time it took to perform the procedure. The target audience is looking to see in what percentile do the elapsed times spill out in. I am not sure where to even begin, but here is an example of what they are looking for:
Min Time 25% -- 50% -- 75% -- 90% -- Max Time
00:01 -- 00:05 - 00:08 - 00:12 - 00:18 - 00:29
How this translates is the min and max times are as you see them. 25% of the time, the procedure was performed in 5 minutes or less. 50% of the time in 8 minutes or less, etc. The percentages are constant, the number of procedures and times are dynamic. Any help would be greatly appreciated, even if only a partial solution. Other than the min and max times, I am not even sure where to begin.
I have tried the XPercentile function listed on "The Access Web," but it will not work for me. I think it is due to the fact that I am trying to do this on times.
I have sent this to other Forums and am still at square one after beating my head against the wall for 3 days.
I see this as a meths based problem. First off I think we need to seek the maximum time for the job. so say 50 minutes is the maximum time.
Therefore 50 minutes is 100%, we then seek the minimum time for the job for example 5 minutes. This involves two queries with whatever other parameters are available / required from this table.
So far our report looks like:
I assume all tasks are recorded in minutes. I assume all tasks are recorded in a table as singles not time date, if they are not do so now. If you need to record hours minutes and seconds then add these through a form consolidating into a single number like:
Hours /3600 + minutes/60 + seconds=total seconds
Or some other format that suits you. If you dont record seconds then store as minutes or what have you.
Next we count the total tasks for the period. For our example we will say 100 samples tasks. This is a query of it's own.
We now count all tasks into groups using top and bottom count queries. THE PROBLEM IS HERE AS ACCESS TENDS TO NOT COUNT ABSOLUTELY CORRECTLY.
We ask for the top 10%. We then ask for a not in the top 10%and rerun a count on the top 10%.
We keep repeating the process until we finally have all the components required for the report.
Finally we assemble all the queries as required to produce a result something like what you require.