Ranking Records and Handling Ties in Microsoft Access with Total Access Statistics

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:

  • Perform rankings adjusting for records with the same value (ties).
  • Place the summary value in a field in your data source
  • Analyze the data in your Access tables and queries (including linked data)
  • Support grouping on field values so you can perform multiple analysis in one pass

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.

Ranking Result Options

Create Output Table

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.

Create Output Table

Assign Rank to Field

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:

Assign Rank

The Ranking Options are divided into Calculation Options and Output Options.

Ranking Options

Ranking Options

The calculation options specify how to rank the values.

Sort Order

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.

Sort Order

Multi-Field Option

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.

Multi-Field Option

Rank Ties

When ties occur, there are three ways to rank the records:

  • Average - Tied values are given the average rank, and the following values reflect the cumulative number of records.
  • Consecutive - Tied values are all given the first rank, and the next value is given next consecutive number, regardless of record count.
  • Cumulative - Tied values are given the first rank, and the following values reflect the cumulative number of records.

Ranking Calculation Options

The following field selection screen appears for Ranking:

  • Group Fields let you rank records for each group (set of unique values in your group fields).
  • The Independent Fields are sorted and assigned rank values for each group.
  • The Weight Field allows you to weight the record by the value in this field.
  • Records with blank values in the independent field or weight field (if assigned) are not ranked.

Ranking Field Selection

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:

  • Financial Cash Flow Calculations with Microsoft AccessFinancial Cash Flow Calculations
    Use discounted cash flows and interest rates to generate Net Present Value (NPV), Present Value (PV), Future Value (FV), Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR) for periodic and date dependent payments and receipts.
  • Percentile Calculations with Microsoft AccessPercentile Calculations
    Calculate different types of percentiles: median, quartiles, quintiles, octiles, deciles, percentiles, every X.5 percentile, and place them in a table or update an existing field with the record's percentile value.
  • Running Totals and Moving Averages
    Summarize a moving set of records to calculate running sums, moving averages, minimum, maximum, range, median, standard deviation, etc.
  • Data Normalization (Data Transpose)
    Transpose non-normalized data so you can easily analyze and maintain it.
  • Regressions
    Simple, multiple and polynomial regressions with calculation of equation coefficients, ANOVA and residual table
  • Crosstabs and Chi-Square
    Advanced cross-tabulations with percentage of row, column and totals displayed as records or columns

Reviews

"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

More Awards Reviews and References in Scientific Papers

Statistics Info

Additional Info

 

 

Free Product Catalog from FMS