Pvteye
asked on
Comparing Access database tables
What we have is two databases on seperate CD's, within those databases are tables that we need to compare from one database CD to the other database CD. The column headings match, we are trying to see the differences in the data in each individual field between the two database tables. Are there any tools that may accomplish this I have tried to search for some and have actually tried ones out, but they were unsuccessfull. Any help on this would be great thankyou
Are you looking for a way to automatically run through the tables and compare the data? This process can be simplified if you can guarantee that the fieldnames match 100%, and there is always one common field that you expect the data to be the same (eg. the unique ID or code).
If this is the case, you can loop through the table definitions of one database. Create a select that joins the two tables on the common field, then loop through the other fields and compare the two tables. You will also need to do left and right joins where the records don't exist in one or the other.
If this is the case, you can loop through the table definitions of one database. Create a select that joins the two tables on the common field, then loop through the other fields and compare the two tables. You will also need to do left and right joins where the records don't exist in one or the other.
ASKER
There are 40 or so field headings and about 190,000 lines of data to be compared, not sure if an automated tool would be better for this or some sort of code for joining the tables and things like that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm sorry to bring up this PAQ's question, but I am also attempting to do the same and follow the same solution... I'm just not sure what is meant by:
"Create a new table (eg. tmp) with a field for the fieldname (FieldName), Value in table 1 (OldValue) and table 2 (NewValue). Then you can try and use the code snippet I attached."
I have done both check queries (outer joins), and no queries have been returned.
"Create a new table (eg. tmp) with a field for the fieldname (FieldName), Value in table 1 (OldValue) and table 2 (NewValue). Then you can try and use the code snippet I attached."
I have done both check queries (outer joins), and no queries have been returned.
Nevermind, I figured it out... just had to read the code to understand.. :)
Thanks for the solution.
Thanks for the solution.
No problem, glad it helped.
Table 1
Employee ID
First Name
Last Name
City
State
Table 2
Employee ID
First Name
Last Name
City
State
Make a query with Employee ID then pull each field from both tables. In essence you end up with a query that has the unique identifier and then both data items for each field. You can then go into the conditional formatting and write syntax like IF Table2. First Name is not equal to Table1. First Name turn it red. If you write that for each of your fields it will essentially highlight everywhere that the tables differ in data.
As I said if you are looking at comparing a lot of field this may not be a feasable option due to time, but it workss OK on a small scale