ACC1x: How to Mark Records with Unmatched or Invalid Values

ID: Q103400


The information in this article applies to:


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 
Issue type        : kbhowto 

Last Reviewed: March 26, 1999