Identify Differences in Fields among different tables

[jgibson24]

Hello,



I'm hoping someone can point me in the right direction of how I might identify data that doesn't match in multiple tables.

I have two tables one from an older system and one from a newer system. Essentially we are trying to identify what may be in the old system data that hasn't made it over to the new system or anything where something in the new system was entered incorrectly (i.e. data doesn't match).

Showing a subset of fields in the old:

ID (PK)	Document ID	Authors
1	1	Don Jones
2	1	Dr. Larry Doby
3	2	Dr. Jonas Gruberstein
4	3	Dr. James Galdoff
5	3	Dr. Isaac Guttenburg
6	3	Dr. Jeffrey Jarvis
7	4	Paul Segretto
8	4	Dr. James Smith

And the new

DetailID (PK)	Book ID	Authors
305728	1	Don Jones
305729	2	Dr. Jonathan Gruberstein
305730	3	Dr. James Galdoff
305731	3	Dr. Isaac Guttenburg
305732	3	Dr. Jeffrey Jarvis
305733	4	Dr. James Smith

My goal would be able to return data where it doesn't match for authors using the book/document ID:

Book/Doc ID	Old	New
1	Don Jones	Don Jones
1	Dr. Larry Doby
2	Dr. Jonas Gruberstein	Dr. Jonathan Gruberstein
4	Paul Segretto	Dr. James Smith
4	Dr. James Smith

Any suggestions on how I might accomplish this?

Thanks,

JG

[burrina]

You need to add the 2 tables to a query. If there is not already an existing relationship between the tables on the fields that contain the corresponding data, you create a join on the fields that you want to examine for matches. You can create as many joins as you want, but each pair of joined fields must be of the same or compatible data type.

HTH

[rpeare]

You're talking about matching non matching text values (i.e. Dr. Jonas Gruberstein vs Dr. Jonathan Gruberstein). The short answer is you are not going to get satisfaction purely from a query. In fact you will likely have to build your own matching VBA code to determine whether something is 'close enough' to be a match. This is the type of algorithm that Google uses when you do web searches, trying to determine what you mean and match it with what it MIGHT be. In short, you are not going to get what you want out of queries alone and it's doubtful you'll get it out of writing your VBA module as well, particularly if your names are a single field and not split into title, firstname and lastname.

The only way I can think to do this efficiently is to make an 'exact match' query then exclude all those values from a final query that would show just the 'unmatching' values from each table, then do a visual inspection to see where the differences lie and fix them in the 'wrong' table.

[NTC]

as Burina notes this is very do-able based on your post. Put both tables in the query design view; then draw a join from Document to Book ID and then right click and option it to show all of one table (it will turn into an arrow). Drag the fields onto the design and when you run it then you will see nulls/blanks where there is no match.

make a second query identical to the first but have the arrow pointing the other directly ; so that you can capture all mismatches between the 2 results....

[jgibson24]

Hello Dave,

Thanks for the reply. I do have a relationship setup between the two tables based on the Document ID / Book ID fields.

The issue I'm having is that I'm not sure how to limit the data to only what is different. What I end up with is every possible combination of the two tables. As an example for document ID / book ID 1 I end up with the following:

Document/Book ID	Table1 Author	Table2 Author
1	Don Jones	Don Jones
1	Dr. Larry Doby	Don Jones
2	Dr. Jonas Gruberstein	Dr. Jonathan Gruberstein
3	Dr. James Galdoff	Dr. James Galdoff
3	Dr. James Galdoff	Dr. Isaac Guttenburg
3	Dr. James Galdoff	Dr. Jeffrey Jarvis
3	Dr. Isaac Guttenburg	Dr. James Galdoff
3	Dr. Isaac Guttenburg	Dr. Isaac Guttenburg
3	Dr. Isaac Guttenburg	Dr. Jeffrey Jarvis

etc...

Any Suggestions on how I limit it so I only get what is missing or different from each table?

Thanks,

JG

[jgibson24]

Hello NTC and rpeare,

@NTC
That is what I think I have setup... In my query I have

Document ID ---> Book ID
Include ALL records from Document and only those records from Books where the joined fields are equal

I set to dsiplay the document ID, Document Author and Books Author and the results are every combination possible, i.e. the example data I have in the previous post.

@rpeare
I'm wouldn't want to match non matching text values. With the Dr. Jonas Gruberstein vs Dr. Jonathan Gruberstein example I'd want that to appear to flag it so that the data in the new table could then be updated to match the old which has already been verified. In the example I'd want both records returned because Dr. Jonas doesn't exist in the old list and Dr. Jonathan doesn't exist in the new list.

Thanks,

JG

[rpeare]

You can't link but just the book number.

Let's assume your tables are TABLE1 and TABLE2 in your original post (in the order they appear)

if you want to show all the records FROM TABLE1 that have no matching value in TABLE2 you would link the two tables by the book/document ID AND author, then put IS NULL on the TABLE2 criteria for the author's name. Make sure this is a DIRECTED join, in this case you can double click the lines connecting the two tables and make sure the arrow is pointing FROM TABLE1 TO TABLE2

Conversely if you want to show all records FROM TABLE2 that have no matching values in TABLE1 would reverse the directions of the arrows and put the IS NULL criteria in the TABLE1 author's name.

[NTC]

as rpeare notes; put the IS NULL criteria in the table the arrow is pointing to ......... this will result in just records from the table the arrow is pointing FROM that have no match in the table the arrow is pointing TO..... reverse arrow and move that IS NULL for the other direction

yes you are going to get repeats depending on how dirty and mixed up is your data in the two sources. this is just a start.... you can also do the No Duplicates query and Duplicates query that are embedded features in the query design area.... one presumes you are trying to construct a new consolidated table - so export these results to excel or write to a new table...and build it step by step....