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