Microsoft Access’ ability to analyze numerical data is significantly extended with Total Access Statistics. Although Access queries are powerful, they don’t perform advanced analyses such as percentiles, frequency distributions, confidence intervals, t-tests, correlations, regressions, weighted functions, probability functions, group analysis, and non-parametric analysis. Prior to Total Access Statistics, to perform these analyses you had to export Access data to another program, such as Excel, or export it to a dedicated statistics package. Not only were these methods cumbersome, formatting data and getting the results back into Access intact was nearly impossible.

Total Access Statistics can perform a variety of statistical functions. The functions it supports are similar to those of comparably priced, stand-alone statistics programs. Its features exceed functions built into commercial spreadsheets and programming environments such as Microsoft Excel and Visual Basic. Total Access Statistics includes relatively simple functions, such as percentiles, frequency distributions, correlations, and crosstabulations. It also includes more sophisticated functions, such as analysis of variance, multiple and polynomial regression, Goodness of Fit tests, and Chi-Square.

Total Access Statistics, lets you statistically analyze any Access table or query in an Access database. The program is fully integrated with Access, and includes a Statistics Wizard that you launch from the Access File|Add-ins menu. Users can easily specify fields to analyze, fields to group by, and statistics to generate. Users don’t need to do any programming, and can analyze several fields simultaneously. These user specifications are automatically saved as a scenario that can be run later. This feature makes it easy to repeat an analysis on the same table as it changes over time. The Wizard lets you manage scenarios with options to name, edit, copy, and delete scenarios.

When you run a scenario, the output is placed in Access tables. You can specify the output table names. Later, you or the users of your application can use Access to do further analysis or integrate the output into forms and reports. Total Access Statistics calculates all numbers with double-precision (15-digit) accuracy. The program eliminates the need to manually type or import results from another statistics program back into Access.

Statistical functions

Total Access Statistics can perform a variety of statistical functions, which are organized into three categories:

Parametric Analysis

Parametric analyses are the most basic and common statistical functions. You use parametric analyses when you assume the numeric data is continuous and normally distributed.

Field Descriptives (analyses of each numeric field) -- Range, variance, coefficient of variance, standard deviation, standard error, sum, sum squared, geometric mean, harmonic mean, root mean square, skewness, kurtosis, mode, confidence intervals using t-Test or Normal distribution methods, t-Test versus Mean, percentiles (median, quartiles, deciles, percentiles), and frequency distributions with user defined number of intervals, initial value, and interval widths.

Field Comparisons (comparison of two fields) -- Count difference, mean difference, correlation, covariance, R-Square, and paired t-Test (degrees of freedom, t-Value, and one-and two-tail probabilities).

Regressions (comparison of two fields) -- Single, multiple, and polynomial regressions (up to 9th order). Options to calculate a Y-intercept, or force the regression line through the origin. In addition to calculating the coefficients of the regression, analyses of each coefficient (standard error, beta, t-value and probability) are also determined; optional residual table shows each data point with actual, estimated, and residual values.

Cross-Tabulations -- Although Access queries can perform crosstabs, Total Access Statistics adds several features, including crosstabs on multiple value fields, automatic generation of column subtotals, and calculation of percent of column, row, and total for each cell. Chi-Square analysis is available to show whether there’s a statistically significant relationship between the row and column fields (degrees of freedom, Chi-Square, probability, Cramer’s V, Phi-coefficient, and coefficient of contingency).

Group Analysis

Group analysis lets you compare between groups of records to detect statistical differences in means. You designate a grouping field (factor) to define the groups. Three options are available: two sample t-Tests (with pooled and separate variance tests), analysis of variance (ANOVA), and two-way ANOVA.

Non-Parametric Analysis

You perform a non-parametric analysis when you’re not working with continuous, normally distributed data (the assumptions of parametric analysis). Total Access Statistics offers a range of functions: sample Chi-Square, sample sign test, Kolmogorov-Smirnov goodness of fit tests (uniform, normal, and poisson distributions), paired sign test, Spearman’s Rho correlation, Wilcoxon Signed Rank Test for matched pairs, Wald-Wolfowitz runs test, Mann-Whitney, Kolmogorov-Smirnov two sample test, Kruskal-Wallis one-way analysis of variance, and Friedman’s two-way analysis of variance.

Statistics Wizard

Total Access Statistics includes a Windows installation program that installs files into its own directory, and updates the Access .INI file (MSACC20.INI). Loaded as an Access library, Total Access Statistics includes a Statistics Wizard that you launch from the Access File|Add-ins menu.

The Statistics Wizard Main Menu displays analysis types (parametric, group, or non-parametric) and corresponding analysis subtypes, such as describe, frequency, and percentiles. A list box displays any previously saved analyses that correspond to the selected analysis type and subtype (figure 1). Total Access Statistics refers to previously saved analyses as “Available Scenarios.”


Figure 1: TOTAL ACCESS STATISTICS WIZARD -- The main form for getting started.

You can edit, copy, delete, display, or run any of the scenarios in the list box on the right side of the main form. Perform any of these tasks by selecting the scenario and pressing the appropriate command button on the Statistics Wizard Main Menu. Create new scenarios by clicking on the New button and providing the information requested in subsequent dialogs. As shown in figures 2 and 3, the user is prompted for an analysis type and subtype, and then for the table or query containing the data to be analyzed.


Figure 2: TOTAL ACCESS STATISTICS SCENARIO DIALOG -- Create your own analysis using this dialog.



Figure 3: TOTAL ACCESS STATISTICS TABLE SELECTION DIALOG -- Pop up this dialog to work with Access data.

Total Access Statistics can only analyze one table at a time. As a result, when the data you want to analyze is contained in the fields of two or more related tables, bring the fields into a single table. You can use a Select query to bring in the fields. Unless you’re going to run multiple scenarios on the same data, there is no benefit to using a Make Table query to place query results in a table; it works fine with data from a Select query.

Once you have selected a table or query, another dialog displays a list of available fields. Depending upon the type of analysis you want to run, select, and assign fields in the list to one of five field types: group, independent (X), dependent (Y), weighting (W), or comparison. Group fields are optional. Use them to define unique sets of records for analysis. Like a Group By statement in a query, a Group field instructs Total Access Statistics to identify all the unique values in the field, and generate a separate analysis (and output record) for each value. When you specify multiple Group fields, Total Access Statistics performs separate analyses for each unique combination of field values. Weighting fields are optional, and often used to weigh records when the data is already summarized. An example might be having a set of records that contains fields for Country, Population, and Per Capita Water Consumption. You might use Population as a weighting field to produce statistics about country and per capita water consumption based on population.

When you’re finished selecting fields, a dialog displays options that vary according to the analysis type and subtype. A final dialog includes fields that let you enter a brief description of the scenario and the names of the Access tables that will store the output. You can save and run scenarios in a single step, or save them. Because Total Access Statistics places output in Access tables, users can control the appearance and reuse results. Like any Access table, you can view, sort, reformat, query, merge, and export the output table, and use the output table as the Record Source for a form or report. These tasks don’t require programming or data conversions.

All decimal places are shown for all numbers. For this review, I analyzed sample data using Total Access Statistics and a handful of commercial applications. When I performed direct comparisons of the results from approximately a dozen functions, I found no inaccuracies in the values generated by Total Access Statistics. In some instances, I couldn’t do a direct comparison because Total Access Statistics offers features that aren’t available in other products. Direct comparisons were especially difficult for cross-tabulations. These functions
support many different types of calculations such as count, sum, average, minimum, maximum, range, variance, coefficient of variance, standard deviation, and standard error. The functions also provide output options, including percent of row, percent of column, and percent of total.

Total Access Statistics understands Access data. If it encounters a numeric field with a null value, it skips the data. Almost every output table includes a [Missing] field showing number of records skipped, and a [Count] field showing data analyzed.

Total Access Statistics is quite fast. During analysis, a status form shows the number of records processed. The program performs most analyses in one, quick pass. Some functions that require significant sorting (percentiles, mode, and some non-parametrics) write to temporary files on disk and take more time. The program’s performance was acceptable compared to Access. Although Total Access Statistics can analyze any Access table, the use of temporary tables may make some analyses inappropriate for analyzing extremely large SQL tables.

Probability Calculator

Another useful feature is the Probability Calculator (figure 4), which calculates probability values for a variety of statistical test values, including Z (normal distribution), student’s t-value, Chi-Square, and F-value. Enter the test value and the degrees of freedom (if necessary), and press the Calculate button. The Probability Calculator displays the corresponding probability value with 15 digits of precision. If you calculate these values by referencing and linearly interpolating values in the Appendix tables of statistics books, this feature is a real time saver.


Figure 4: BONUS FEATURE -- Probability Calculator with F-Value option.

Access Basic Interface

Programmers and power users will appreciate the Access Basic interface that allows you to use programs to execute your saved scenarios. The interface has a single function that requires arguments for the scenario number (the unique ID assigned to each scenario) and the message display (which accepts values of True or False to indicate whether you want to see progress information while the analysis is executed). If you set the message display to False, you can use your own interface and users will never know Total Access Statistics is running. A separate function that corresponds to the Probability Calculator generates the probability values for the values you specify.

User Manual

The Total Access Statistics manual does a good job of explaining the steps for creating and running scenarios. It has helpful information about analysis options and their associated output, and helps users with issues related to data preparation. The manual notes that statistical functions are optimized for normalized data and then supplements this information with step-by-step instructions for converting non-normalized, spreadsheet-type data into a normalized format.

Conclusion

With an intuitive, easy-to-use Wizard interface and the Access Basic (or VBA) interface, Total Access Statistics makes it easy for users or programmers to perform statistical analysis. It generates the analyses you need with output in a useful format—tables. If you need to perform advanced numerical analysis, consider Total Access Statistics.