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
Latest Versions and Enhancements
Additional documentation is included with the Total Access Statistics
product, including a printed User’s Guide and context sensitive help
system.
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.
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 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.
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 and 2010: 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.
A wide range of statistical calculations is available:
Descriptive Field Analysis
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
-
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 (50th
percentile)
-
Quartiles (every 25th
percentile)
-
Quintiles (every 20th
percentile)
-
Deciles (every 10th
percentile)
-
Octiles (every 12.5th
percentile)
-
Every 5th 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
Additional information for
Calculating Percentiles in Microsoft Access
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 (or Interpolations)
Regression Types:
-
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 Options
Cross-Tabulation and Chi-Square
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
-
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 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
Financial Analysis
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
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 Nth
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 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
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
This section contains the information you need to quickly get up and
running with Total Access Statistics.
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.
A professional setup program is included.
Just follow the steps and point to the
folder to install 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 2010 Wizard
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,
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!
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.
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.
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.
Total Access Statistics is available via Electronic Software
Download (ESD) and a physical version which includes a CD with the
add-in program, runtime library, and on-line help system. A 180 page
professionally printed user manual is also included describing the
program and the definition of each calculation.
FMS offers free and paid technical support via phone, e-mail,
and through our
newsgroups. 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, 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).
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.
All Our Microsoft Access Products
Single
