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

The result options determine how the ranked records are retrieved or displayed. The result options determine how the scenario should behave when run.

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.

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:

The Ranking Options are divided into Calculation Options and Output 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.

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.

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.

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

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
  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
  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