Unmatched Query is giving invalid results.

[WAsh]

I'm upgrading a rather primitively designed database with approximately 4500 records. I've established certain base tables that are properly linked. One of the tables is associated to the old database by a Primary Telephone Number. When I run a Find Unmatched Query based on the Telephone Number, it returns over two-hundred records that are obviously matched. I've made certain that the field-size properties match, and that the formatting and input masks are identical, and that the fields are indexed. Any ideas on a solution (the database has even been compacted and repaired) would be highly appreciated.<br>Thanks.

 

[DougP]

Yes I think you have the table selection backwards<br>When you create an unmatched query it asks for 2 tables.<br>Try picking the tables in reverse order and see if that works.<br>

 

[elizabeth]

Also you have to be careful which fields you specify as &quot;matching&quot; versus &quot;also show&quot;.

 

[WAsh]

Thanks for your input, Elizabeth and Doug...but, sorry, it still pulls invalid results.&nbsp;&nbsp;Either way I choose the tables, and regardless of what fields are shown in the result, the unmatched query continues to perform with a high degree of inaccuracy (in one case 50%). I welcome any other thoughts you might have.<br>Again, Thanx.

 

[DougP]

Well you need to investigate &quot;Invalid results&quot; as you call it.<br>Start out by doing simple part or just a few records and get it working then build on it.<br>Access is doing what it does. Sometimes we don't know what its doing or why its doing it.<br>You have results in front of you and you have to figure out why they do not match what you expect.<br><br>How do you walk a hundred miles? Start with the first step.<br><br>

 

[Cornerstone]

Most likely the input mask was added after a couple hundred records were entered. Meaning the records appear to be identical but in fact in one table the number is STORED differently than in the other table. If you remove the formatting and input masks from both tables and then look you will probably find the records that were stored differently. You could also try to export the table without formatting and look and find the records in question.

 

[elizabeth]

You could try pasting your query (the SQL behind it, found under View/SQL) in here for us to look at.

 

[WAsh]

Well, thanks for each of your responses. Having just received them, I've had no chance to work with them, but, following is the SQL Statement created by Access that gives the results (and, by the by, this is the first time I've had difficulty with Unmatched Query results).<br><br>SELECT DataFOC.OrderID, DataFOC.LastName, DataFOC.FirstName, DataFOC.Notes, DataFOC.PurchaseOrderNumber, DataFOC.CusClass, DataFOC.TrType, DataFOC.PhoneNumber, DataFOC.SubmitDate, DataFOC.[LSR ID#], DataFOC.BillingAddress, DataFOC.PhysicalAddress, DataFOC.City, DataFOC.State, DataFOC.PostalCode, DataFOC.MidInit, DataFOC.AdlList, DataFOC.AdlTN, DataFOC.AdlTN2<br>FROM DataFOC LEFT JOIN AIDs ON DataFOC.PhoneNumber = AIDs.PhoneNumber;<br><br>Again Thanks.

 

[WAsh]

Cornerstone, your suggestion proved the correct diagnosis. When going through the unformatted Telephone Number fields of the table, I found almost 500 records that maintained the input mask; and, after manually getting rid of the parentheses, spaces, and hyphens, my duplicates disappeared from the unmatched query. Thanks. And, thanks to Elizabeth and Doug for lending your expertise.