Total Access Statistics is the most powerful data analysis program for Microsoft Access. It runs as an Access add-in and offers a wide range of statistical functions to analyze your data.
How do I assign percentiles to my individual records with Total Access Statistics?
Using a recent version of Total Access Statistics (versions 16, 15, 14, 12, or X.7 or X.8).
The recent versions of Total Access Statistics lets you update a field in your table with the percentile value for that record. These are the percentile calculation options:
Notice that you can assign the percentile value to a field in your table with an option to handle ties (multiple percentiles for the same value) by assigning either the low or high percentile to it.
Visit this page for additional information on Calculating Percentiles in Microsoft Access with Total Access Statistics.
Using older versions of Total Access Statistics (pre-X.7 versions)
If you are using an older version, this feature does not exist, but you can still get the results by running a query. With the Percentiles option under Parametric analysis, Total Access Statistics generates the value for each percentile. The output table (default name TAS_Percentiles) shows in each of its records, the percentile number and the corresponding value. With this table, you can create a query linking back to your original table to show the percentile of each record.
Using the SAMPLE table in the SAMPLE.MDB, create a scenario that calculates Percentiles and put its results in the TAS_Percentiles table. This query displays the percentile for each record in the SAMPLE table:
SELECT Sample.ID, TAS_Percentiles.State, Sample.Age, Max(TAS_Percentiles.Percentile) AS Percentile
INNER JOIN TAS_Percentiles ON Sample.State = TAS_Percentiles.State
WHERE (((TAS_Percentiles.DataField)="Age") AND ((TAS_Percentiles.Value)<=[Age]))
GROUP BY Sample.ID, TAS_Percentiles.State, Sample.Age
ORDER BY Sample.Age
Paste the SQL string above into a new query’s SQL View. Alternatively, from the Query Designer:
Follow these steps:
- Place the SAMPLE and TAS_Percentiles tables on the query
- Link the Group fields between the tables ([State])
- Select from the Sample table, the key field ([ID]), group field ([State]) and the field analyzed ([Age])
- Select from the TAS_Percentiles table the [Percentile] field
- Make the query a Totals query (select Totals from the View menu) with "Group By" for ID and Age, and "Max" for Percentiles. You should also rename the Percentile field, otherwise it will appear as "MaxOfPercentile"
- Specify criteria for two fields in the TAS_Percentiles table: [DataField] should be "Age" for the name of the field being analyzed, and the [Value] field should be "<=[Age]"
- View the results
The query basically, gets for each record in the original table (Sample), the corresponding largest percentile value in the TAS_Percentiles table. Criteria is placed on the TAS_Percentiles table to make sure the data for the correct field is used. The sort by [Age] field is optional, but clearly shows how the records are ranked.
You can change the query to a Make-Table query (under the Query menu) to save the results in another table.
Please sign in to leave a comment.