Need access tool to compare 2 table for the difference in the data

[SKV]

I am pretty sure that everyone run into to the task of comparing 2 versions of the data. Though I have created databases in the past for the comparison, the limitation is that from time to time the table changes and so the fields in the tables (example: in one project it required to determine changes in sales forecast, another project requires to determine changes in project dashboards)

I am seeking help and guidance from the experts in this group to help design a dynamic tool for the following requirements.
1. flexibility for the user to select tables (in different dbs and directories)
2. identify the primary key (may have more than 1 field) in for the tables)
(Assuming both source 1 and source 2 have same data structure, may have additional fields but primary key combination should be same)
3. Prompt user to select the field they want to compare (default should be all the fields)
4. Create a resultant table showing the change details (all records form the source1 (S1) and source2 (S2)

Example:
step 1: user selects 2 tables in 2 different database with different table names but same table structure and same primary key combination.
[TABLE="width: 229"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Table: Source_1[/TD]
[/TR]
[TR]
[TD]Key[/TD]
[TD]Group[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Z[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]Z[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]e[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 267"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Table: Source_2[/TD]
[/TR]
[TR]
[TD]Key[/TD]
[TD]Group[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]z[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Z[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]h[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]x[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]e[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Output:
[TABLE="width: 756"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 9"]Table: ComparisonS1S2[/TD]
[/TR]
[TR]
[TD]Rcd Source[/TD]
[TD]Key[/TD]
[TD]Group_1[/TD]
[TD]Group_2[/TD]
[TD]Group_ChngFlag[/TD]
[TD]Score_1[/TD]
[TD]Score_2[/TD]
[TD]Score_ChngFlag[/TD]
[TD]OverallChangeFlag[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]Null[/TD]
[TD]Changed[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]Changed[/TD]
[TD]Changed[/TD]
[/TR]
[TR]
[TD]S1-S2[/TD]
[TD]B[/TD]
[TD]Y[/TD]
[TD]z[/TD]
[TD]Changed[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]Same[/TD]
[TD]Changed[/TD]
[/TR]
[TR]
[TD]S1-S2[/TD]
[TD]C[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD]Same[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]Changed[/TD]
[TD]Changed[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]D[/TD]
[TD]Null[/TD]
[TD]h[/TD]
[TD]Changed[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]Changed[/TD]
[TD]Changed[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]E[/TD]
[TD]Null[/TD]
[TD]x[/TD]
[TD]Changed[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]Changed[/TD]
[TD]Changed[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]P[/TD]
[TD]Z[/TD]
[TD]Null[/TD]
[TD]Changed[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]Changed[/TD]
[TD]Changed[/TD]
[/TR]
[TR]
[TD]S1-S2[/TD]
[TD]R[/TD]
[TD]e[/TD]
[TD]e[/TD]
[TD]Same[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]Same[/TD]
[TD]Same[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
Note: rcd source tells which table source had the data so its legend
S1 : rcd was present just in Source 1
S2 : rcd was present just in Source 2
S1-S2: record is present in both the sources

I look forward to the guidance to develop this tool or please advice if this is already available somewhere.

Thanks in advance

regards
SKV

 

[strive4peace]

Compare Two Access Files and Identify Differences, by John Michefske
Downloads - Tigeronomy Software

 

[SKV]

Compare Two Access Files and Identify Differences, by John Michefske
Downloads - Tigeronomy Software

I checked this link and the tool - it is a good tool to compare the 2 databases but it does not compare the data within the tables/queries.

Thanks for your help though.

 

[SydneyGeek]

This could provide a good starting point.
Comparing two or more lists in Access -- DataWright Information Services
The task is reasonably complex because you need to build outer joins in both directions, then combine that to get the final result. the article shows how to do it for a single field; each subsequent field would need to be handled separately if you went the crosstab route, but the Union query approach might be extensible.

Denis

 

[strive4peace]

I am wondering why you have multiple tables with the same information... this can get confusing! Do you have a MASTER database where you consolidate everything?

> "flexibility for the user to select tables (in different dbs and directories)"

use code to link to those tables and change their names (presuming they already exist in the active database) ... ie -- add a qualifier to the end so the name is different. when linking to an external table, NAME is what the table is called in the local database ... SourceTableName is its name in the source database.

sounds like a mess to me ... don't envy you. Could you explain more about your process and how Access gets its information? thanks

 

[SKV]

Crystal : If you read my request completely then what I am trying to say is from time to time we get different versions of the same file and we have to compare what is changed. So I want a tool to compare data information between 2 versions of data and see what has changed. If you like we can discuss it in more details may be over phone or chat.

I am wondering why you have multiple tables with the same information... this can get confusing! Do you have a MASTER database where you consolidate everything?

> "flexibility for the user to select tables (in different dbs and directories)"

use code to link to those tables and change their names (presuming they already exist in the active database) ... ie -- add a qualifier to the end so the name is different. when linking to an external table, NAME is what the table is called in the local database ... SourceTableName is its name in the source database.

sounds like a mess to me ... don't envy you. Could you explain more about your process and how Access gets its information? thanks

 

[strive4peace]

Hi SKV (what is your name?)

talking privately defeats the purpose of a public forum, which is designed to help everybody. I am happy to help you here ... how comfortable are you with VBA? that is what I use to make comparisons ...