Microsoft Access Crosstab and Chi-Square with Total Access Statistics
Total Access Statistics extends the basic
cross-tabulations available in Microsoft Access.
Easily select additional calculation types,
percentages of row,
column and total. The percentages can be
created as separate records or as fields in
the same record. Additionally, Chi-Square and expected
value statistics can be generated to measure
the randomness of the distribution.
Total Access Statistics generates
crosstabs from your
Microsoft Access tables and queries. All results
are in MS Access tables that you can use on your forms,
reports, and VBA code.
||Total Access Statistics
Multiple Value Fields
% of Column, Row, Total
Expected Cell value
Expected Cell %
Results in Tables
Two output tables may be created: Cross-tabulation and
Introduction of Crosstabs in Microsoft Access
Cross-tabulations show summaries of one field
across two fields in a matrix. Crosstabs compare the row
field to the column field to display
relationships between the values in those
two fields quickly in a "spreadsheet" like
matrix. Each unique value in the column
field is a field in the crosstab result.
Crosstab is a Microsoft Access Query Type
Microsoft Access Query Designer for Crosstabs
Distribution of Counts for State and Gender
Crosstabs and Chi-Square Features in Total Access Statistics
Total Access Statistics makes it very
easy to generate crosstabs and Chi-Square analysis in MS Access:
- Analyze the data from Microsoft Access
tables and queries (including linked
- An unlimited number of records may be calculated in one pass.
- Optionally specify a weighting field
- Optionally specify a value or range of
values to ignore
- Specify one or more ROW fields
- Specify one COLUMN field
- Specify one or more VALUE fields
All the options can be selected
interactively through the add-in Wizard
interface. VBA developers can run the
analysis through a
programmatic VBA function
Selecting Fields for Crosstabs
After selecting your data source, field selection is very simple:
Field Selection for Crosstabs: Three value
fields are being processed at one time
Specify at least one row field. The row field(s) becomes
the left column(s) of the crosstab. If you choose more than
one row field, totals are calculated for the first R-1 row
fields (where R is the number of row fields); basically, a
separate crosstab for each set of unique values in the first
R-1 row fields.
Specify only one column field. Each unique value in the
column field becomes a field in the crosstab.
Specify the fields to be calculated (Sum, Count, etc.)
and placed in each of the crosstab's cells. Unlike the
Microsoft Access crosstab queries, multiple value fields can
be processed at one time.
As with other types of analysis, you can optionally
specify a Weight field. You can also optionally ignore a
value or range of values.
After you select your fields and press the [Next] button, the
Crosstab and Chi-Square Options in Total Access Statistics
Choose among ten Cross-Tabulation types,
Percentage, and Chi-Square options:
This specifies the type of calculation to perform. If you select a type other than Count, the
crosstab generated includes an extra row that shows the
Count of each cell. For instance, for Standard Deviation,
you would see both the standard deviation for each cell and
the number of data points used to calculate the value.
You may optionally calculate each cellís value (based on
the Cross-Tabulation Type) relative to its row, column, or
total summaries. For percent of column and total, the cell
is compared to the corresponding value for its subset of
records (unique values in R-1 rows).
Chi-Square options are available (visible) if the
calculation type is Count or Sum. Chi-Square measures the
independence of the distribution between the row and column
There are several Chi-Square options. For the main
crosstab table, you can add % of Expected values or the
Expected value for each cell. Expected values are calculated
for each subset. You can also create a separate Chi-Square
table that calculates the Chi-Square for each crosstab
Show Percentage Value As Option
When you choose to calculate Percentage Options, the
values can be shown as rows or columns in the output table.
If you choose Rows, the percentage values are shown as
additional rows with the value in the DataType field
designating what the values are in the row.
If you choose Columns, the percentage values are shown as
additional columns, so if you have a column called
"Virginia", there would be additional columns like "Virginia%Row",
"Virginia%Column", "Virginia%Total", etc. This makes it easy
to create reports which show the value and itís percentages
on each row.
Note that because this adds more fields, it can be a
problem if your Column field has a lot of unique values
(Access has a 255 field limit per table). The default
setting is Rows to allow the most unique column values.
The Chi-Square fields (% Expected and Expected) are also
displayed in Columns rather than records if you choose the
See the output table examples for the result differences.
Crosstab Output Table
This is a crosstab of the Sample table
examining Sex vs. State, with the count of the
Age field. This quickly shows how many people
fall in each combination of Sex and State:
Crosstab Output Table with Percentage Values
Data Type Field
The [DataType] field shows each recordís calculation
including the percent calculations and "GROUP Total" for the
||Cell value where type is the calculation type
(Count, Sum, Min, Max, etc.)
||Cell's percent of row summary
||Cell's percent of subset column
||Cell's percent of subset total
||Cell's percent of expected value
||Cell's expected value using Chi-Square calculation
||Group (subset) summary: Total, %Row, %Col, %
Row Summary Field
The [RowSummary] field shows the total for the row. This
is the summary of the input table data associated with the
row and not the values in the crosstab. For instance, the
row's average is not the average of the crosstab's cells.
A field is created for each unique value in the column
field. This is case insensitive, i.e. "AAA" is the same as "aaa".
If the column field contains a null value, a field named
<Null> is created.
Showing Percentages as Columns in the Crosstab
If the option to Show Percentage Values
is set to columns, the output looks like this:
Crosstab Output Table with Percentage Values
in Columns (truncated with just the first
set of fields)
The data is identical to the option with
the values in rows, but displayed in
Chi-Square Output Table
Chi-Square (also known as Pearson's Chi-Square) is a test
to determine if the variables in the crosstab row and column
fields are independent. The results are placed in a separate
table. One record is created for each subset (unique
combination of the first R-1 fields, where R is the number
of row fields). If there is only one row field, only one
record is created per value field.
Chi-Square is only calculated if the Crosstab type is
Count or Sum. If a Sum crosstab is selected, all values in
the Value field must be positive. Chi-Square analysis of Sum
crosstabs is intended for data that contains summed counts.
Chi-Square Output Table
Chi-Square Output Fields
||Field name identifying the value field
||Number of records (or sum of weightings)
||Minimum cell's expected value
||Maximum cell's expected value
||Number of columns with data (some subsets may have
no values for a column)
||Number of rows with data
||Degrees of freedom: = (C-1) x (R-1)
||A sum over all the cells for the difference
between the observed and expected values
||Probability that the rows and columns are
independent based on Chi-Square and DF
||Coefficient of Contingency statistic
Interactive Wizard and VBA Programmatic
Total Access Statistics lets you interactively generate
your calculations through its
wizard interface to easily select the data
source, fields, and analysis without
programming. After you specify your
selections, they are automatically saved as
a scenario in your database so you can run
them again in the future when your data
statistics function is also available so
you can generate these results
from code. You can easily run any saved
scenario by calling a single procedure in
the Total Access Statistics VBA library. The
library includes a royalty-free runtime
license so you can include it with your
Access database and distribute it to
non-Total Access Statistics owners.
Call the function from a button's OnClick
event or other process, and the results are
generated for you. You can even hide the
built-in user interface so your users don't
even know Total Access Statistics is
running. They'll be amazed with your
statistical analysis capabilities!
Additional Details of Calculations Available in Total Access Statistics
Here are some additional resources and details about some of the data
analysis you can perform on your Microsoft Access data with Total Access
- Financial Cash Flow Calculations
Use discounted cash flows and interest rates to generate Net Present Value (NPV), Present Value
(PV), Future Value (FV), Internal Rate of Return (IRR), Modified Internal Rate of Return (MIRR)
for periodic and date dependent payments and receipts.
Calculate different types of percentiles: median, quartiles, quintiles,
octiles, deciles, percentiles, every X.5 percentile, and place them in a
table or update an existing field with the record's percentile value.
Running Totals and Moving Averages
Summarize a moving set of records to calculate running sums, moving
averages, minimum, maximum, range, median, standard deviation, etc.
Data Normalization (Data Transpose)
Transpose non-normalized data so you can easily analyze and maintain it.
Simple, multiple and polynomial regressions with calculation of equation
coefficients, ANOVA and residual table
Crosstabs and Chi-Square
Advanced cross-tabulations with percentage of row, column and totals
displayed as records or columns