esn,

When I write an application that requires a frequent import of data from
some other source, I generally use a technique similar to Johns, but
slightly different.

1. I start out by writing a procedure to either link to the external data,
or import it into a temporary table. I prefer to use a temp table, so that
I can add an autonumber field to the table so that I can easily identify
each record (assumes that the table does not already contain a unique PK).

2. I then identify all of the fields in that table that contain data that
should exist in one of my "lookup" tables.

3. I then loop through each of the fields/lookup tables from step #2. In
a listbox, I display all of the values in that field that don't match with a
value in the appropriate lookup table. The query for the listbox would look
something like:

SELECT DISTINCT [Field1]
FROM tbl_Import
LEFT JOIN tbl_Lookup
ON tbl_Import.Field1 = tbl_Lookup.Field1
WHERE tbl_Lookup.Field1 IS NULL
ORDER BY tbl_Import.Field1

Next to the list, I add a combo box (cbo_ChangeTo) to allow the user to
select one of the values already in the "approved list". I also add a
textbox (txt_ChangeTo), to allow the user to change what is in the table
that is being imported to a new value that I also want added to my "approved
list". Both of these controls are implemented by a button (cmd_ChangeTo)
which determines which one of the controls has a value (if the user selects
from the combo, the textbox is cleared. If the user types in the texbox,
the combo value is set to NULL) and updates the field value in the table
being imported, and in the case of the textbox, adds the value to the
"approved list". I also have a button that allows the user to simply add
the value selected in the list to the "approved list". After either of
these buttons is clicked, and the code is run, I requery the list.

4. You could easily extend this procedure to record the mismatches in the
table that Piet mentioned and run the update queries for each of the fields
identified in step #2 prior to step #3. This would improve your processing
and over time significantly reduce the number of mismatches.

5. Once I have done this, then I run an append query to append the data
(with the appropriate ID values) to my master table. This append query uses
INNER JOINs between the import table and all of the lookup tables (this
ensures that only those records where all of the fields with "lookup" values
have been corrected or had changes made to the lookup table). Finally, I
delete the values from the import table that have been added to my master
table (leaving only those records where there is still a data mismatch).
This is where the autonumber field mentioned in step #1 comes in handy.

6. At this point, you can either go back to step #3 to correct those
records, or go to a more tedious manual process.

HTH
Dale


"esn" wrote in message
...
John - there's one more thing I forgot to ask - given your query setup
what happens to records in which the plant code (the lookup value) is
correct, but data in another field violates a validation rule? Would
I have to set criteria in the queries to test all of the fields in the
source data?