Total Access Statistics Product Guide

Thank you for your interest in Total Access Statistics. FMS is pleased to provide you with this Product Guide to assist in your evaluation of this product. The contents of this document are organized as follows:

Contents

• Total Access Statistics Overview
• Key Features and Benefits
• Data Analysis Functions
• Up and Running in Ten Minutes
• General Information
• Licensing and Pricing
• FMS Experience with Statistics
• Conclusion

Latest Enhancements

• New Features for Access 2007, Version 12
• New Features for Access 2000, 2002, and 2003, Version x.7

Additional documentation is included with the Total Access Statistics product, including a printed User’s Guide and online, context sensitive help system.

Total Access Statistics Overview

The Problem

There are many statistical programs in the market, but they are not designed specifically for Access or databases 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

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.1 Meg) needs to be distributed.

Winner of the Access Advisor Reader’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.

Key Features and Benefits

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 MDB/Jet 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: 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.

Data Analysis Functions

Descriptive Field Analysis

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

• Count, Missing (blanks), Mean
• Minimum, Maximum, Range, Sum, Sum of Squares
• 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
• t-Test vs. Mean

Describe Options

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

Percentiles: Median, Quartiles, Deciles, etc.

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 with special handling for ties

Percentile Options

Frequency Distributions

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

Frequency Distribution Options

Field Comparison - Compare fields in a record

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

Regressions

Regression Types:

• Simple (one dependent and one independent field)
• Multiple (one dependent and many independent fields -- up to 40+)
• Polynomial (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 Options

Cross-Tabulation and Chi-Square

• 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
• Sorted column fields with special handling for blank values
• Chi-Square Analysis: Chi-Square, Probability, Cramer's V, Phi-Coefficient, Coefficient of Contingency

Running Totals

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

Group Analysis - Compare groups of records

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)

Record Analysis

Random Record Selection

• Generate or flag a subset of your records
• Select every N^th record, 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

Non-Parametric Analysis

• One Sample Chi-Square
• One Sample Sign Test for Median, Mean, and Custom Value
• Kolmogorov-Smirnov Goodness of Fit Test
• Paired Sign Test, Spearman's Rho 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

Probability Calculator

• 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

Up and Running in Ten Minutes

This section contains the information you need to 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

Running the Program

Total Access Statistics 2007 Wizard

Wizard Interface

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, please 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!

Programmatic Interface

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.

For complete details of Total Access Statistics
calculations and features, get the trial version or
download the product help file (included in the trial version)

General Information

System Requirements

• 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

FMS offers free and paid technical support is via phone, fax, e-mail, and through our newsgroups. Technical support options are described here.

Licensing and Pricing

Total Access Statistics Pricing by Access Version:
License	Price	2007	2003	2002	2000	97
Single	$599
5 Seat	$1799
Upgrade from Prior Access Version
Single	$399
5 Seat	$1199
Upgrade to .7 within the Same Version
Single	$149	N/A
5 Seat	$449	N/A
Premium Support Subscription
Single	$299	More Information
5 Seat	$999
Also Available as part of
Total Access Ultimate Suite
Runtime/Redistributable Version
Total Access Statistics includes a runtime library with royalty-free redistribution rights. Use is subject to the terms of the license agreement.
Licensing Information
Total Access Statistics is licensed on a per user basis. Each user who runs the program must have a license.

FMS Experience with Statistics

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 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, with a background in engineering and numerical analysis, and Dan Haught, executive vice president, a Microsoft MVP, and co-author of the Microsoft Jet Engine Programmer’s Guide (Microsoft Press).

Conclusion

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.

Reprints and use of extracts from this document are permitted.