Total Access Statistics

Total Access Statistics Manual
Microsoft Access 2016 Version
is Shipping!

New 14.1 Version for
MS Access 2010!

New X.8 Versions for
Access 2007, 2003, 2002, 2000, and 97!

View all FMS products for Microsoft AccessAll Our Microsoft Access Products

Statistics Info:

Why Statistics?

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

Additional Info:



Perform statistical analysis on Microsoft Access dataPerform statistical analysis on Microsoft Access dataNumerical Analysis for Microsoft Access data Numerical Analysis for Microsoft Access: Download the Trial Version of Total Access Statistics

Frequently Asked Questions for Total Access Statistics

Pre-Sales Questions/How Does it Work?

Technical Support Questions

Pre-Sales Questions/How Does it Work?

What version of Total Access Statistics do I need? What versions of Microsoft Access does Total Access Statistics support?

Total Access Statistics is a Microsoft Access add-in and is Access version specific. For example, Total Access Statistics 2016 only works as an add-in for MS Access 2016.

However, Total Access Statistics works with databases in any format that is supported by the specific version of Access. For example, Total Access Statistics 2010 works with MDB databases that are in Access 2000 or Access 2002-2003 format, Access 2007-2016 format (ACCDB), and Access Data Projects (ADPs), provided the database is opened in Access 2010.

The following versions of Total Access Statistics are available:

For information on the history of Access versions, visit our Microsoft Access Version Releases, Service Packs, Hotfixes, and Updates History page.

Do I need to import/export data to Total Access Statistics?

No. There is no need to export data to Total Access Statistics or import data from it.

Total Access Statistics runs as an Access add-in. Simply invoke it from the add-ins menu and it's ready to analyze the data in your currently opened database. Data from your local tables, linked tables, and any query that returns records (SELECT or CROSSTAB queries) can be analyzed. For ADPs, any View or Stored Procedure that returns records can be analyzed.

All the results are in tables, so it's entirely integrated within your database.

Do I have to know programming to use Total Access Statistics?

No. Total Access Statistics can be used without any programming.

It runs as an Access add-in which you can invoke right off the Microsoft Access ribbon/menu. A Statistics Wizard appears to guide you through the selection of your data source, the fields to analyze, the calculations to generate, and the output table names.

Your settings (not your data) are automatically stored so you can run them again in the future.

Will Total Access Statistics let me add statistical analysis to my Access application?

Total Access Statistics includes a runtime library and distribution license that lets you add statistical analysis directly in your Access application. Simply add our library to the referenced libraries from your VBA IDE, and you're ready to invoke our code.

Your users will be amazed at the advanced statistical analysis you can generate with a button click, or maybe the data just shows up on their report or form.

Easily run one of the scenarios you've created with a simple procedure call. Options are available to specify whether your users see our program running or not if you want to hide it.

For more information, visitMicrosoft Access Statistical Functions using VBA in Total Access Statistics

Note: The runtime library does not include the add-in's user interface to let users specify their own calculations. If your users need this feature, each user needs their own license of Total Access Statistics.

Does Total Access Statistics support Access Data Projects (ADPs)?

Yes. The Total Access Statistics supports all Access database formats that its version of Access supports including ADPs. ADPs are supported by Microsoft Access 2000 through 2010.

It can analyze data from your ADP (SQL Server) tables, views, stored procedures, and user defined functions that output records. You can even provide parameters.

This feature was added with version 12 for Access 2007, and versions 11.7, 10.7, and 9.7 for Access 2003, 2002/XP, and 2000 respectively. Pre-X.7 versions did not support ADPs. Existing customers can upgrade for a nominal fee.

How does Total Access Statistics analyze my data?

Total Access Statistics analyzes the data from your table and queries (for ADPs, it supports views, stored procedures, and user defined functions). The way it analyzes data is similar to the way you run an Access/SQL query where you can optionally "Group By" fields you want to perform separate calculations on, and "Sum" on fields to analyze. Multiple fields can be grouped and analyzed at one time.

For instance, if you had demographic data, you could group by "State" and perform percentile calculations on the "Age" and "Income" fields to see the percentiles for age and income (separately) for each State. If you didn't Group By "State", you'd simply get the percentile values for all the records.

You can also designate a weighting field, or a value or ranges of values to ignore in any of the calculations.

The results are provided in tables, so you can perform further analysis on them or include them in your queries, forms, reports, and code.

Your specifications are automatically saved so you can run it again.

For more details, see the Product Guide.

How many records can Total Access Statistics analyze at one time?

Total Access Statistics can analyze an unlimited number of records.

How fast is Total Access Statistics?

Total Access Statistics is optimized to be extremely fast. For most calculations, it only requires one pass through your data to generate the results. Speed is mostly controlled by how fast it can read your data (imagine a recordset reading every record) and writing the results to the output table. The statistical calculations themselves are very fast once the data is retrieved.

In most cases, the performance is similar to a regular query. There are some calculations that require sorting (e.g. percentiles), which take a bit more time depending on the amount of data being analyzed.

How accurate is Total Access Statistics?

All calculations are performed in double precision with 16 digits of accuracy. Extensive testing has been conducted to ensure accuracy with our program and against other commercial statistical analysis products.

Having created commercial statistical analysis programs since 1990 and Total Access Statistics since 1993, FMS has a long track record of delivering solid results for mission critical solutions.

Does Total Access Statistics create graphs?

No. Total Access Statistics generates its output in tables. These tables can be used by Access in forms and reports, or exported to other products like Excel, where you can apply the built-in graphing features.

Can I try Total Access Statistics before I buy it?

Absolutely! Download the Trial Version to try Total Access Statistics on your own data before you buy it. Scenarios created with the Trial Version are preserved so that you won't need to recreate them when you get the retail version.

Also, remember that all FMS products come with a 30-day money back guarantee, so you can buy with confidence!

Does Total Access Statistics Require Administrator Rights?

Total Access Statistics does not require Administrator rights if it is installed for current user.

Total Access Statistics does require Administrator rights if it is installed for local machine.

Does Total Access Statistics support database security?

Yes! Total Access Statistics automatically respects your security settings since it's running within your database environment and not outside of it. So whether your database has a password, Jet security, encryption, or other login requirements, Total Access Statistics has the same rights you have to analyze the data you can see.

This applies to whether it's running as an add-in or through its runtime library.

How do I get more information on how Total Access Statistics works?

Several additional resources are available for you:

  • A PDF flier
  • The Product Guide gives a detailed overview of the product features
  • Download the user manual in PDF format with detailed descriptions of every option and calculation available to you
  • Download the help file with detailed descriptions of every option and calculation available to you
  • Download the Trial Version and try it on your own data (includes the help file)

If you still have questions, please contact us so we can help you understand how Total Access Statistics can fulfill your data analysis needs.

Technical Support Questions

If I upgrade to a new version of Total Access Statistics, can I use my existing saved scenarios?

Yes, Total Access Statistics is completely backward compatible. Every version of Total Access Statistics runs scenarios created in earlier versions. Just convert your database to the new version of Access, and Total Access Statistics (for that version of Access) can run them.

Why do I have the error "Update Wizard not initialized properly " when trying to run the Update Wizard for Total Access Statistics?

This error occurs when you attempt to run the Update Wizard by double clicking the "Update.exe" directly instead of running it from the Windows Programs Menu shortcut.

This is because the update.exe file requires parameters to run and return the correct information. In order to determine if there is an update available for this product, please run the update wizard program from the Windows Programs menu under All Programs, FMS, Total Access Statistics, Update Wizard.

Total Access Statistics won't run programmatically

If the scenario runs properly when invoked from the Add-in wizard, but fails programmatically, there is usually a problem with how the Total Access Statistics database is loaded as a library.

Make sure the runtime library of Total Access Statistics is loaded as a library reference. This is set from a module under the Tools, References menu.

Read the manual chapter or refer to the help file's section on Advanced Topics for details on running Total Access Statistics programmatically. See the SAMPLE database included with the program for examples.

Can I run a Total Access Statistics scenario from a macro?

Yes, you can run a Total Access Statistics scenario from a macro. To do so, refer to the Advanced Topics chapter of the Total Access Statistics manual. You must understand how to prepare your database to reference the Total Access Statistics library, the definition of the TAS_Statistics function, and each of its parameters.

Create a macro and add a RunCode action. In the function name section, enter "=TAS_Statistics" and its parameters. For instance:

    =TAS_Statistics(5, "", "", "", True)

where 5 is the scenario number, and True sets the MsgDisplay parameter (True displays progress info to the user, False hides it completely).

How can I perform analysis in Total Access Statistics without cluttering up my database with output tables?

Since Total Access Statistics creates output tables in the current database, you may clutter your database with unnecessary tables.

Using a Separate Database

If you are performing a lot of ad hoc analysis and want to keep it separate from your main data tables, create a new database and link to your data tables. This keeps the results of Total Access Statistics separate from your main database. If you are not familiar with using linked tables, consult your Microsoft Access manual or help file.

How do I assign percentiles to my individual records with Total Access Statistics?

Using a recent version of Total Access Statistics (versions 15, 14, 12, or X.7 or X.8)

The recent versions of Total Access Statistics lets you update a field in your table with the percentile value for that record. These are the percentile calculation options:

Microsoft Access Percentiles

Notice that you can assign the percentile value to a field in your table with an option to handle ties (multiple percentiles for the same value) by assigning either the low or high percentile to it.

Visit this page for additional information on Calculating Percentiles in Microsoft Access with Total Access Statistics

Using older versions of Total Access Statistics (pre-X.7 versions)

If you are using an older version, this feature does not exist, but you can still get the results by running a query. With the Percentiles option under Parametric analysis, Total Access Statistics generates the value for each percentile. The output table (default name TAS_Percentiles) shows in each of its records, the percentile number and the corresponding value. With this table, you can create a query linking back to your original table to show the percentile of each record.

Using the SAMPLE table in the SAMPLE.MDB, create a scenario that calculates Percentiles and put its results in the TAS_Percentiles table. This query displays the percentile for each record in the SAMPLE table:

SELECT Sample.ID, TAS_Percentiles.State, Sample.Age, Max(TAS_Percentiles.Percentile) AS Percentile
FROM Sample
INNER JOIN TAS_Percentiles ON Sample.State = TAS_Percentiles.State
WHERE (((TAS_Percentiles.DataField)="Age") AND ((TAS_Percentiles.Value)<=[Age]))
GROUP BY Sample.ID, TAS_Percentiles.State, Sample.Age
ORDER BY Sample.Age

Just paste the SQL string above into a new query’s SQL View. Alternatively, from the Query Designer:

Microsoft Access percentiles

Follow these steps:

  1. Place the SAMPLE and TAS_Percentiles tables on the query
  2. Link the Group fields between the tables ([State])
  3. Select from the Sample table, the key field ([ID]), group field ([State]) and the field analyzed ([Age])
  4. Select from the TAS_Percentiles table the [Percentile] field
  5. Make the query a Totals query (select Totals from the View menu) with "Group By" for ID and Age, and "Max" for Percentiles. You should also rename the Percentile field, otherwise it will appear as "MaxOfPercentile"
  6. Specify criteria for two fields in the TAS_Percentiles table: [DataField] should be "Age" for the name of the field being analyzed, and the [Value] field should be "<=[Age]"
  7. View the results

Basically, the query gets for each record in the original table (Sample), the corresponding largest percentile value in the TAS_Percentiles table. Criteria is placed on the TAS_Percentiles table to make sure the data for the correct field is used. The sort by [Age] field is optional, but clearly shows how the records are ranked.

You can change the query to a Make-Table query (under the Query menu) to save the results in another table.

Does Total Access Statistics include a function which makes it possible to convert z-scale to percentile?

Yes. Total Access Statistics includes probability features that convert Student's Z Test score to its probability value. It's part of the Probability Calculator in the add-in that also supports t-Value, Chi-Square and F-Value with degrees of freedom. A VBA function is also available to call it from code.

How can I use Total Access Statistics to flag outliers?

Total Access Statistics determines calculations such as standard deviations and confidence intervals, but does not automatically determine outliers. Since the results are in a table, you can easily create a query based on your definition of an outlier, and select your records by linking our table to yours.

Total Access Statistics Error 3219: Invalid operation when attempting to run a scenario

If you are running a Total Access Statistics scenario and it encounters an error while trying to read your data, your table may contain corrupt data.

This is particularly true if the error log says it crashes here:


where FieldName is the name of your field with the invalid data.

While we trap for many types of invalid and missing data, we are receiving reports of corrupt data that we have been unable to reproduce in our test environments. It may be related to linked tables to non-Access data sources.

To see if there's a problem reading data from a field in your table, insert this VBA function into a module in your database and run it:

Public Function TAS_TestFieldValues(ByVal strTable As String, ByVal strField As String) As Boolean
  ' Comments: Procedure invoked by user to test if all values from a table's field can be read.
  ' Params  : strTable       Table name
  '           strField       Field name
  ' Return  : True if successful, False if not
  ' Created : FMS, Inc.

  Dim fOK As Boolean
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim varTest As Variant

  fOK = False
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strTable, dbReadOnly, dbSeeChanges)

  With rst
    Do While Not .EOF
      ' Crashes here if the value can't be read
      varTest = rst.Fields(strField)
  End With
  fOK = True

  Set dbs = Nothing

  TAS_TestFieldValues = fOK

End Function

You can call the function from the Immediate Window, and pass the name of your table and field for the parameters:

? TAS_TestFieldValues("TableName", "FieldName")

It attempts to read every value of a field in all the records of your table. It will crash on the record if the field can't be read.

Note that this code uses DAO and works within a Jet database (ACCDB or MDB) and not an ADP database file type.

Please let us know if this helps you find bad data in your field and what the value is. Thanks.