Find Data Differences Between Microsoft Access Tables and Queries
Total Access Detective finds data differences between tables and queries
in your Access databases. The data can be from an Access database or any
linked table. New, deleted, and modified records are detected. For
modified records, the different fields are identified.
Results are shown in forms for you to view, export to a table in your
database, or print. A powerful Combine feature lets you merge data from
your two source tables into a new table in your database.
Compare Different Data Sources
Find changes to your data from:
- Any two tables in one database (MDB, ACCDB, or ADP)
- Any two SELECT queries in one database (MDB or ACCDB)
- Any or all identically named tables in two databases (MDB or
ACCDB)
Data Comparison Results
Keyed Tables
For keyed tables (so matching records are compared), these differences
are found:
- Records in one table and not the other (could be new or
deleted)
- Modified records with their individual field differences
The results are presented in these formats:
- Records in one table but not in the other
- A summary for each modified record where all the differences
(no matter how many fields) are shown in a text field on one
record.
- A detailed field-by-field list of modifications, showing
each field value side-by-side.
Unkeyed Tables
For tables without a key field or queries where the first field is not
designated as the primary key, records are compared sequentially and the
first record that's different is documented.
Viewing the Data Comparison Results
The data comparison results are shown on two tabs:
Every record that's different is shown with its primary key(s),
difference type, and details. For modified records, the field
differences, number of fields, and field names are listed.
For each modified record, every field difference is shown
side-by-side
While viewing the results, there are several options:
- Reports: Several reports are available for you to
print, preview, report view, or export to file in formats like
Adobe PDF, HTML, snapshot, text, etc.
- Export: To export the data differences to a table in
your database
- Combine: To merge the two data sources into a new
table
Data Comparison Options
A variety of options are available for you to customize how to compare
the two data sets, and what's generated:
Record Identifier for Non-Keyed Tables and Queries
For keyed tables, the key field is automatically used to identify
records for comparison. For unkeyed datasets, Total Access Detective's
default behavior is to compare the records sequentially and identify the
first record that's different, which tells you the tables do not contain
the same data but not much more.
You can perform more advanced analysis if the first field defines
uniqueness for each table. This is particularly useful when comparing
queries which never have a key field. With this, new, deleted, and
modified records can be identified.
Specify Sequential Comparison or the First Field as a Unique
Identifier
Compare Tables with Different Fields
Total Access Detective lets you compare data in tables with
identical and "similar" structures by specifying the Field
Comparison methodology:
Specify How Fields are Matched for Comparison
- All field names and types match
- Compare fields by order regardless of field names, provided
field types and number of fields match
- Compare identical field names regardless of field order;
other fields are ignored
Documentation to Generate for Differences
For every modified record, Total Access Detective creates a
record documenting the difference and filling a memo field with
details of every differing field and its values in the two records.
This may be a lot of information. To reduce this, you can limit the
maximum number of character per record and/or a limit on the amount
of text to show for memo fields which are different.
Control How Much Data is Shown for Each Modified Record
Displaying Field Differences for Modified Records
The memo field containing all the field differences in a modified
record can be stored with one field difference on each line along
with its two values, or a separate line for each field value to make
it easy to compare values visually.
Display Field Differences on One Line or a Separate Line for Each
Field
Ignore Small Differences Between Numbers
Specify the level of numerical accuracy. Decide whether you want
to see all numeric differences, or only differences that exceed a
certain percentage. This lets you avoid small rounding errors and
immaterial differences.
Specify the Minimum Percentage Difference for Comparing Numbers
Combining Data into a New Table
Once data differences are documented, a powerful feature lets you
combine the data from your two tables into a new table in your database.
Visit Merge (Combine) Data from Two
Microsoft Access Tables
for more information.
Total Access
Detective Home
All Our Microsoft Access Products
