Microsoft KB Archive/103400
ACC1x: How to Mark Records with Unmatched or Invalid Values
The information in this article applies to:
- Microsoft Access versions 1.0, 1.1
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.
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.
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
Issue type : kbhowto
Last Reviewed: November 4, 2000