ACC1x: How to Mark Records with Unmatched or Invalid Values

• 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