Microsoft Access does not offer built-in features to rank records. Ranking records is also tricky if there are tied values.
Fortunately, Total Access Statistics performs a wide range of record ranking calculations across your records:
Rankings were added to Total Access Statistics for Microsoft Access 2007, and X.7 versions for Access 2003 and earlier.
The result options determine how the ranked records are retrieved or displayed. The result options determine how the scenario should behave when run.
The output table includes a record for each ranking showing the value of the Independent X field and the number of records with that value.
If multiple fields were selected, and the Multi-Field Option was set to Together, each of the field's values is a separate column.
If Separate was selected, a field called [DataField] contains the field name and [Value] its value.
Instead of, or in addition to, creating an output table, you can assign the rank value to a field in the data source for each record.
In this example of a golf tournament results, the Total field is ranked in the Ranking field:
The Ranking Options are divided into Calculation Options and Output Options.
The calculation options specify how to rank the values.
Rank your data in ascending or descending order. If first place is for the lowest value, like golf scores, choose Ascending. If the first rank is for the largest value, like number of wins or highest ratings, choose Descending.
If you selected more than one independent (X) field, you can specify whether you want the fields sorted together or separately.
The Together option assigns each record one rank value. Records are sorted by the first field, then the second, third, etc. If there are ties in the first field, the second field breaks the tie. If all the fields have the same value, the records are considered tied and given the same value.
The Separate option treats each independent (X) field separately. The output table contains a field with the X field name, its value, and its rank. If you have more than one independent field and choose Separate, the Assign Rank to Field option is not available since a separate rank is given to each independent field.
When ties occur, there are three ways to rank the records:
The following field selection screen appears for Ranking:
Ranking Field Selection
Total Access Statistics lets you interactively generate your calculations through its wizard interface to easily select the data source, fields, and analysis without programming. After you specify your selections, they are automatically saved as a scenario in your database so you can run them again in the future when your data changes.
A VBA statistics function is also available so you can generate these results from code. You can easily run any saved scenario by calling a single procedure in the Total Access Statistics VBA library. The library includes a royalty-free runtime license so you can include it with your Access database and distribute it to non-Total Access Statistics owners.
Call the function from a button's OnClick event or other process, and the results are generated for you. You can even hide the built-in user interface so your users don't even know Total Access Statistics is running. They'll be amazed with your statistical analysis capabilities!
Here are some additional resources and details about some of the data analysis you can perform on your Microsoft Access data with Total Access Statistics:
Version 14.1 for
Microsoft Access 2010
Version X.9 for
Microsoft Access 2007 and 2003
Version X.8 for
Microsoft Access 2002, 2000, and 97
"Total Access Statistics performs a wide range of data analysis right inside Access. The product includes a Statistics Wizard to perform parametric, group, non-parametric, record analysis and more. The results are placed in Access tables that you can integrate into your forms and reports."
Alison Balter, Author