I'm going to assume you can compute the percentile by hand. (If you can't, Access will probably not do it for you.) I'll show you the way I would approach it, but in the final analysis, you are the one who will have to make the formulas work. OK? Let's start...
You can help yourself by reading up on DSum, DMax, DMin, DLookup, and DCount in the Help Files including taking a quick peek at the examples. This forum also has threads containing the function names. Write a parameter query (q.v. in the Help Files) to select AND SORT (!!!) the subset of your total data set (if that's the way you are keeping things). Then later, you will run the Dxx functions on the query rather than the whole table. Should be faster.
You said you were willing to have a button do this for you. If you haven't done so by now, read up on the OnClick event on a form. When building a button on a form, have the form's control wizards enabled at the time. One of the "presets" for building buttons is "Run Code" - and if you have the entry point already defined, you will be allowed to run a subroutine. (NOT a function! It should not return a value.)
For the code, read up on opening recordsets in code segments. In the code, do a DCount on the parameter query before you open it in order to determine the total population. Keep that in a code-local variable declared through a Dim statement. Open the recordset and do a .MoveFirst on it.
In a loop, read through the recordset represented by the query until the .EOF property becomes true. For each value you find in the recordset, do a DCount of the number of entries less than or equal to the current value. Then your percentile is (something like)
pctile = CINT( DCount(values <= this value)*100 / DCount(total) )
but... remember that you never have percentiles of 0 or 100.
For very large groups, this forumula COULD give you a percentile < 1 (say, at least 201 members in population and a single value equal to DMin(query). I'm also guessing that for really big populations and a unique value equal to DMax(query), you might get a percentile of 100. So you will need some "IF" statements in the code to catch these two cases. NOTA BENE: This is also similar to rank-ordering, the difference being that for rank-order, you DON'T divide by the DCount(total) AND you have to show the ranges where there is a tie. But a similar approach for rank-ordering is also valid.
OK, having computed this value, you already have the recordset open. Sorting and searching don't necessarily make a query non-updateable. So do a .EDIT of the recordset, copy/set the computed percentile in a field that you include in the recordset, and do the .UPDATE of the recordset. Then step to the next record (.MoveNext) and loop until you reach the exit condition of the loop. Close the recordset and you are done.
This is a misleading situation from the DB purist standpoint. Mathematically, the percentile formula as I just described SHOULD be computable on the fly, but the syntax of Access makes that a somewhat tricky proposition. Normally, you would say NEVER store a computed value. However, there are exceptions to every rule. If you were willing to work with a pug-ugly formula, I would say don't store it, just compute it as you need it. But since the values depend on the population as a whole of a parameter query that could select subsets, the nastiness of the formula is an EXCUSE for violating an otherwise solid concept, "don't store a computable value." And not a bad excuse as such things go.
