ACC1x: How to Mark Records with Unmatched or Invalid Values

Last reviewed: May 14, 1997
Article ID: Q103400
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1

SUMMARY

A table imported from an external source may contain invalid or erroneous data. This article demonstrates how to use an update query to compare an imported table to a reference table and mark the imported records that do not match the reference table values.

In Microsoft Access version 2.0, this functionality is provided by the Find Unmatched Query Wizard. For more information, search for "Find Unmatched Query Wizard" using the Help menu in Microsoft Access 2.0.

MORE INFORMATION

For the following example, assume that you have imported a customer order table and have found that the records in the imported table have valid but misspelled customer names. The example below demonstrates how to compare names in the imported table with a table of valid names and mark the imported records that do not match the reference table.

  1. Create two tables. One table will be called Reference Table and the other will be called Imported Table. Create a field called Customer Name in each table, and add the following entries:

          Reference Table   Imported Table
          --------------------------------
          ACE               ACE
          BAT               ACE
          CAD               ACA
          CAT               BAT
                            BAR
                            CAD
                            CAD
                            CAT
    
    

  2. Add a field named Matched to the Imported Table. Assign the Yes/No data type to this field. Note that the default values in this field will be set to No.

  3. Create a query based on both tables. Join the tables by dragging the Customer Name field from the Reference Table to the Customer Name field on the Imported Table. Then choose Update on the Query menu.

  4. Fill out the query grid with the following entries:

          Query: Mark Unmatched
          ----------------------------------------------
          Field name: Customer Name
    
             Table: Imported Table
             Criteria: [Reference Table].[Customer Name]
          Field name: Matched
             Table: Imported Table
             Update to: "Yes"
    
    

  5. Run the query. The Matched field will be set to Yes for all records in the Imported Table that have a Customer Name that matches a value in the Customer Name field of the Reference Table.

REFERENCES

Microsoft Access "User's Guide," version 1.0, Chapter 7, "Designing Action Queries and Parameter Queries, page 176


Additional query words: unmatched misspell spell
Keywords : kbusage QryMktbl
Version : 1.0 1.1
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 14, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.