ACC1x: How to Mark Records with Unmatched or Invalid Values
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.
- 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.
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