Compare 2 tables and identify unmatched fields 1

[pvg]

The goal of the project is to achieve very high accuracy for our database from our data entry operators.&nbsp;&nbsp;Two different operators would perform data entry on our records into 2 different tables and then a query would compare the 2 tables to identify unmatched fields. Our Quality Control Dept would get the report and resolve the unmathed records.&nbsp;&nbsp;Once both tables are 100% matched, the report would show no unmatched records.<br><br>These 2 tables have the same structure and identical field names. The data for Table #1 was keyed in by Operator #1 and the data in Table #2 was entered by Operator #2.&nbsp;&nbsp;The Ukey field is a unique key that allows the two tables to be joined like a persons Social Security Number.&nbsp;&nbsp;It is required and never null.&nbsp;&nbsp;The Name & Addr is required BUT the Phone may be null.<br><br>The number of records in the tables may not be the same because an operator may have accidently skipped a record.&nbsp;&nbsp;Therefore, we can start with Table#1 being the master. <br><br>Table_Op#1<br>Ukey, Name, Addr, Phone, EnteredBy<br>Table_Op#2<br>UKey, Name, Addr, Phone, EnteredBy<br><br>Please help me with the Query and report that would show which fields are unmathched.

 

[DougP]

Well if you click the Queries Tab and click New<br>You will see at the bottom of the list<br>&quot;Find Unmatched Query Wizard&quot;<br>follow the prompts and that's it.<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[elizabeth]

BTW, sounds like you'll have to do 2 of these Unmatched queries (one from each side)&nbsp;&nbsp;and union the results. For example if you just do &quot;records in A that are not matched in B&quot;, you may not see any records that in B that are missing in A.

 

[pvg]

To Elizabeth and DougP:&nbsp;&nbsp;Thank you for replying.&nbsp;&nbsp;The answers you gave were at the record or row level and I am looking for unmatched FIELDS.&nbsp;&nbsp;&nbsp;Your answers to this problem refer to the unmatched query wizard which does not solve the problem of matching each FIELD and listing the UNMATCHED FIELDS.&nbsp;&nbsp;&nbsp;The unmatched query wizard only compares ONE field from each table and shows the unmatched ROW by using is null.&nbsp;&nbsp;&nbsp;

 

[DanAuber]

If you created a dummy field linking all the fields using the & function - would you be able to compare these ???<br><br>Thus Ukey & Name & Addr & Phone<br><br>I hope this helps <p>Dan Auber<br><a href=mailtoanAuber@aol.com>DanAuber@aol.com</a><br><a href=

http://members.aol.com/_ht_a/danauber/myhomepage/>Dan

Auber's Home Page</a><br>

 

[elizabeth]

pvg, Take another look. The unmatched wizard can match one <b>or more</b> fields in the same record.

 

[pvg]

To DanAuber:&nbsp;&nbsp;Thank you for replying.&nbsp;&nbsp;I have already tried your suggestion and the result is that you just get a bigger key to match on but you still do not know which FIELD was unmatched.

 

[pvg]

To Elizabeth.&nbsp;&nbsp;Thank you for replying.&nbsp;&nbsp;While the unmatch query wizard offers multiple fields to select, the result is a list of null records which do not show which FIELDS are unmatched.&nbsp;&nbsp;Again I am looking for this:<br>When 2 records match on a unique index:&nbsp;&nbsp;Which FIELDS from those records are different.&nbsp;&nbsp;For example, consider this<br>Table1<br>Ukey to join&nbsp;&nbsp;&nbsp;&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Addr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Phone&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Entered by<br>12345&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Joe Smith&nbsp;&nbsp;&nbsp;123 Elm&nbsp;&nbsp;&nbsp;&nbsp;742-6666&nbsp;&nbsp;&nbsp;&nbsp;pvg<br><br>Table2<br>12345&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;John Smith&nbsp;&nbsp;123 Elm&nbsp;&nbsp;&nbsp;&nbsp;742-6666&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;mhw<br><br>Operator pvg entered Joe Smith for record 12345 and Operator mhw entered John Smith.&nbsp;&nbsp;&nbsp;The query I am looking for would state that the &quot;NAME&quot; FIELDS do not match for Ukey 12345.&nbsp;&nbsp;&nbsp;&nbsp;Thanks again for all your help.