Total Access Statistics Product Guide

The Problem

As the most popular database program on the PC, Microsoft Access databases store more and more data every day increasing the need to analyze it statistically. Unfortunately, the built-in tools available within Access are limited in their ability to perform advanced data analysis. To generate statistical analysis beyond the rudimentary features of Access queries requires a great deal of programming.

There are other statistical programs on the market, but they are not designed specifically for Microsoft Access and usually require learning a different programming language and terms. Additionally, they require exporting data into those programs, performing the analysis there, and then importing the data back into Access. In some cases, the results are provided as text files that need to be parsed in order to integrate back into Access. This approach is not only time-consuming, but error-prone and usually cannot be automated.

The statistical analysis programs that are available also tend to be large stand-alone programs licensed on a per user basis.

The Total Access Statistics Solution

Total Access Statistics solves these problems by giving users a wide range of statistical analysis functions with the features and options database users and developers expect. Calculations such as percentiles, correlations, confidence intervals, regressions, t-Tests, running totals, group analysis, non-parametric analysis, and probability calculations can be easily generated.

Designed specifically for Microsoft Access, Total Access Statistics works directly in Access as an Access add-in. Simply open the database containing the data to analyze, then invoke Total Access Statistics from the menu.

The interactive Statistics Wizard makes it easy to select the data, fields, and statistical options to perform. All settings are automatically saved for re-use. Any Access table, linked table (including SQL Server, Oracle, dBase, Paradox, FoxPro, etc.), or SELECT query can be analyzed. Total Access Statistics also supports ADPs with direct use of SQL Server tables, views, stored procedures, and user defined functions.

An unlimited number of records and multiple fields can be analyzed at once. Best of all, the results are in tables that can be viewed, sorted, queried, or analyzed further. They can also be merged with other data, and added to forms and reports. Eliminate the need to export data or import results.

Total Access Statistics can also be added to Microsoft Access applications. Developers who need statistical analysis in their databases can by-pass the Wizard and add all the statistical features directly into their applications with a simple procedure call. Users don’t even need to know that Total Access Statistics is running in the background. A royalty-free runtime distribution license is included to allow developers to distribute such applications to non-Total Access Statistics owners. Only one file (2.5 MB) needs to be distributed.

Winner of the Access AdvisorReader’s Choice Award for “Best Business Product” multiple times, Total Access Statistics is the most powerful data analysis tool for Access and is simply the best solution for generating complex statistical analysis in Access.

Total Access Statistics includes a complete set of features to make it easy to add powerful data analysis to Access projects.

Data Analysis Features

• Analyze an unlimited number of records and multiple fields at one time.
• Output results are placed in tables in your database.
• Analysis selections are automatically saved as scenarios which can be run against your new data in the future.
• Optionally specify fields to group sets of records.
• Optionally weight records based on the value in a field.
• Optionally ignore a numeric value or range of values.
• All calculations in double-precision.

Data Sources

• From an Access ACCDB/MDB (Jet Engine) database, analyze data from:
  • Microsoft Access tables
  • Linked tables
  • Select queries
  • Crosstab queries
• From an Access Data Project (ADP), analyze SQL Server data from:
  • Tables
  • Views
  • Stored procedures
  • User defined functions
• Specify values for queries, stored procedures, and user defined functions that require parameters

User Features

• Runs directly from the Microsoft Access menu:
  • Access 2007 or later: Database Tools ribbon, Add-ins
  • Access 2003 or earlier: Tools, Add-ins menu
• Interactive Wizard interface requires no programming.
• Code Generator automatically generates code for running statistical analysis and probability calculations in your applications.
• Single user and network compatible.
• Programmatic interface to add statistical analysis to your applications.
• Runtime library with royalty-free distribution license.

For each field, calculate a wide range of descriptive values:

• Count and Missing (null values)
• Minimum, Maximum, Range, Sum, Sum of Squares, Mean
• Variance, Coefficient of Variance, Std. Deviation, Std. Error
• Geometric Mean, Harmonic Mean, Root Mean Square
• Skewness, Kurtosis
• Mode, Mode Count
• t-Test versus Mean (test for null hypothesis)
• Confidence Intervals (t and Normal tests). 95% and user-specified levels

Describe Options

Describe Output Table with Grouped By [State] and analysis on Age, Weight, and Cholesterol fields (not all output fields are shown)

For each field, calculate percentiles:

• Percentile Types:
  • Median (50^th percentile)
  • Quartiles (every 25^th percentile)
  • Quintiles (every 20^th percentile)
  • Deciles (every 10^th percentile)
  • Octiles (every 12.5^th percentile)
  • Every 5^th percentile
  • Every Percentile
  • Every 0.5 Percentile
• Generate percentile values in a separate table with percentile values in each row or values in columns
• Update a field in an existing table with the record's percentile value or other percentile options such as deciles, octiles, quartiles, etc.
• Special handling for tied percentile values

Percentile Options

Additional information for Calculating Percentiles in Microsoft Access

Frequency distribution with user-specified group width, initial value and number of intervals. Display count, percent of total, sums and cumulative.

Frequency Distribution Options

Compare fields in a record

• Mean difference, Standard Deviation of difference
• Covariance, Correlation, R-Square
• Paired t-Test, Probability (1 or 2 tails)

Regression Types

Regression Calculation Options

• Simple or Linear Regressions (one dependent and one independent field)
• Multiple Regressions (one dependent and many independent fields -- up to 40+)
• Polynomial Regressions (one dependent and one independent field -- up to 9th order)

Options

• With Y-Intercept or Zero-Intercept
• Coefficient analysis: Standard Error, Beta, t-Value, and Probability
• ANOVA fit of line: R-Square, Standard Error of Estimate, and ANOVA with sum of squares, mean squares, f-value and probability
• Displays data Count and Missing values
• Residual calculation for each data point
• Update a field in the data source with the record's expected Y value

Regression Result Options

10 types: Count, Sum, Average, Minimum, Maximum, Range, Variance, Coefficient of Variance, Standard Deviation, Standard Error

• Option to use Crosstab queries as data sources
• Count crosstabs for Text and Date value fields
• Row total and Column sub-totals
• Percent of Column, Row, and Total displayed as rows or columns
• Sorted column fields with special handling for blank values
• Chi-Square Analysis: Chi-Square, Probability, Cramer's V, Phi-Coefficient, Coefficient of Contingency

Crosstab Output Table with Percentage Values in Columns (truncated with just the first set of fields)

Additional information in Advanced Crosstabs and Chi-Square in Microsoft Access

Perform running totals such as average, sum, count, median, min, max, etc. over a sorted list of records. Totals can be for the entire list or for a moving number of records (e.g. the last 10 records) which is useful for seeing trends. Up to 5 fields can be updated at one time. These types of running totals can be performed:

• Average (Mean)
• Count, Sum, Sum Squared
• Minimum, Maximum, Range
• Median, Mode, Mode Count
• Standard Deviation, Variance, Coefficient of Variance, Standard Error
• Geometric Mean, Harmonic Mean, Root Mean Square
• Skewness, Standard Error of Skewness
• Kurtosis, Standard Error of Kurtosis

Sample options for Running Totals on Sales over 10 records with 5 Calculations

Additional information for Calculating Running Totals and Moving Averages in Microsoft Access

Periodic and irregular (date dependent) cash flow analysis:

• Net Present Value (NPV), Present Value (FV), Future Value (FV)
• Internal Rate of Return (IRR)
• Modified Internal Rate of Return (MIRR)

Additional information for Performing Financial Cash Flow Analysis in Microsoft Access

Two Sample t-Tests

Select a comparison field with two unique values to define the groups of records to compare. The number of records in each group may be different and the t-test takes that into account to determine whether the two groups are statistically similar.

• Pooled t-Test with degrees of freedom, t-Value, and Probability
• Separate t-Test with degrees of freedom, t-Value, and Probability

Analysis of Variance (ANOVA)

Select a comparison field with more than two unique values to define the groups of records to compare. Calculate:

• Main, Residual and Total values
• Sum of squares, degrees of freedom, mean squares, F-Value and probabilities

ANOVA results in an Access table
(multiple ANOVAs can be created in one pass based on the group fields you specify)

Two way ANOVA

Select two comparison fields to define the groups of records to compare. Different combination of unique values in the two fields define groups which are analyzed independently and together. Calculate:

• Main effects by field (factor)
• Interactions
• Explained effects
• Residual and Total
• Sum of Squares, DF, Mean Square, F-Value, and probabilities

Two Way ANOVA results in an Access table
(multiple ANOVAs can be created in one pass based on the group fields you specify)

Random Record Selection

• Generate or flag a subset of your records
• Select every N^threcord, a fixed number of records, or random % of records

Options for Selecting or Flagging Random Records

Rank Records

• Assign a numeric rank to each record based on its value
• Sort in ascending or descending order
• Options to assign rank with ties (assume records 2 and 3 are tied):
  • Average (ties are 2.5, and record 4 is 4.0)
  • Consecutive (ties are 2, and record 4 is 3 -- the next unique value)
  • Cumulative (ties as 2.0, and record 4 is 4.0 -- the record value)

Options for Specifying How Records are Assigned Rank Values

Normalize Data

Easily transform multiple fields from a non-normalized data source into a new normalized table with a label field and value field.

Example of non-normalized "spreadsheet" data

Normalized results with column values from the source transformed to records

Additional information for Data Normalization (Transpose) in Microsoft Access

• One Sample Chi-Square
• One Sample Sign Test for Median, Mean, and Custom Value
• Kolmogorov-Smirnov Goodness of Fit Test (Uniform, Normal, Poisson)
• Paired Sign Test, Spearman's Rank Correlation
• Wilcoxon Signed Rank Test for Matched Pairs
• Wald-Wolfowitz Runs Test
• Mann-Whitney U Two Sample (non-matched) Test
• Kruskal-Wallis One Way Analysis of Variance
• Friedman's Two Way Analysis of Variance

• Evaluate probability of test values: Z, t, Chi-Square, and F. Enter value, degrees of freedom and the probability appears.
• Calculate inverse probability. Enter a probability and calculate the equivalent Z, t, Chi-Square, and F value.
• Eliminate interpolating values in reference tables.

Probability Calculator

Quickly get up and running with Total Access Statistics.

Upgrading from Previous Versions

All versions of Total Access Statistics are fully compatible with prior versions. Scenarios created in earlier versions can automatically be used in later version with no changes.

Separate versions of Total Access Statistics are available for all versions of Access. Just as you can have multiple versions of Access on the same machine, you can have multiple versions of Total Access Statistics running on the same machine (at the same time). Just install the versions in different directories.

Installation Instructions

A professional setup program is included. Just follow the steps and point to the folder to install the program.

Running the Program

A sample database is included with Total Access Statistics in the directory where you installed the database. Start Access, then open the SAMPLE.MDB database. From the Access ribbon, select Database Tools, Add-ins (for Access 2003 or earlier, choose Tools, Add-ins). The Statistics Wizard appears:

Total Access Statistics 2013 Add-In Wizard

Wizard Interface

The Statistics Wizard looks and feels like an Access wizard, and guides you through the data analysis process with no programming required. Perform powerful analyses with point-and-click ease. Your selections are automatically saved as “scenarios” for re-use. A detailed and indexed on-line help is available.

To run an existing scenario, press the [Run] button. The data is analyzed and results presented in the output table or tables. To create a new scenario, press the [New] button and follow the steps of selecting the analysis to perform, the data source, the fields to analyze, the calculation options, and the output table names. It couldn’t be any simpler!

The results are placed in Access tables the way an Access user or developer expects, and can be easily analyzed further or integrated into forms and reports.

Programmatic Interface

A programmatic interface is included for Access VBA programmers wishing to incorporate statistical functions directly into their applications. Options allow you to specify the input and output tables, and to “hide” Total Access Statistics so your users don’t realize how you implemented such complex formulas!

Adding the analysis is also very simple. By using the wizard to interactively create, run, and refine the analysis, the scenario can be easily added to code by simply referencing the Scenario ID number. The complex requirement of setting analysis options and properties in code is completely eliminated by leveraging the interactive Wizard interface.

An example of using the programmatic interface is available in the Trial program, in the form called: Total Access Statistics Sample. To make this work, you may need to manually set the reference from the database to our statistics library.

Total Access Statistics includes a royalty-free runtime license to let you freely distribute your applications using Total Access Statistics calculations to non-Total Access Statistics owners.

Here's more information on adding Microsoft Access Statistics Functions with VBA.

System Requirements

The requirements for Total Access Statistics are similar to those of Microsoft Access:

• Any Windows version supported by the version of Access
• Microsoft recommended memory for Access and your data
• Less than 10 MB of disk space for Total Access Statistics

Total Access Statistics supports any desktop or network installation of Access.

Product Packaging and Technical Support

Total Access Statistics includes the add-in program, runtime library, and on-line help system. It is available via Electronic Software Download (ESD) and a physical version which includes a CD and 180 page professionally printed user manual.

FMS offers free and paid technical support through our support forum. Technical support options are described here.

FMS has shipped commercial statistical analysis programs for over two decades supporting multiple database and programming platforms and versions. Our first statistics program, ParaStat, for Borland’s Paradox DOS database program was released in 1990. In 1994, we released our first statistics programs for Windows: Total Access Statistics for Microsoft Access 2.0, and ParaStat for Windows for Paradox for Windows. In 1998, we released Total VB Statistics for Visual Basic, and in 2002, Total SQL Statistics for SQL Server using VB or VB.NET. Over the years, we've enhanced Total Access Statistics with each new version of Microsoft Access.

The statistical functions included in our products are based on years of customer feedback and selected for their popularity and usefulness for database analysis. The calculations are intended to fill the gap between the rudimentary features in MS Access and the high-end, very advanced (and expensive) statistical programs. The advantage of our products is their tight integration with their database platform, programmatic control, and output in a format (tables) that can be used by developers in their application. They significantly exceed the needs of most developers and users.

Additionally, our products, which include a royalty-free runtime distribution license, are priced below the single user copies of most high-end statistics programs.

About the Authors

Total Access Statistics is designed and written by Luke Chung, president and founder of FMS, and a Microsoft MVP with a background in engineering and numerical analysis, and Dan Haught, Microsoft MVP, and co-author of the Microsoft Jet Engine Programmer’s Guide (Microsoft Press).

Total Access Statistics is the most powerful data analysis tool available for the Microsoft Access development environment. Able to analyze large data sets stored in Microsoft Jet/Access databases or any linked database, Total Access Statistics combines power and ease of use.

While not every Access developer is going to need statistical analysis, when they do, Total Access Statistics will be a godsend. After all, how many developers want to spend time writing the matrix inversion math necessary to calculate a regression when they can get it with a few clicks of the mouse in Total Access Statistics?

We invite you to put the product through its paces with the trial version, and contact us if we can provide any additional assistance in evaluating or deploying Total Access Statistics.