Microsoft KB Archive/103400

{|
 * width="100%"|

ACC1x: How to Mark Records with Unmatched or Invalid Values

 * }

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.

  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  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. 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.  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"  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.