Lookup Field Issues

  • Thread starter Thread starter esn
  • Start date Start date
John W. Vinson said:
is an "unmatched query" which will find all records in the input file
which do
NOT have matching MyData. You can then manually correct the errors in
tblBadLookup and run another append query resembling the first one to
insert
them.
Click to expand...

John - just spotted this after I posted my suggestion. Wasn't trying to
steal your thunder, sorry :)

Keith.
 
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
 
I like the disclaimer, but why is it "necessary"?

Has the FTC finally done something stupid like finding a newsgroup responder
liable for some code that a user implemented that deleted all of their data,
or something like that?

Dale
 
Dale

I was recently "introduced" to a FCC ruling that seemed to indicate that
they could apply their rules about touting products to anyone even
mentioning a product. If I recall, there was something about soccer moms
blogging a product that they had received, but failing to disclose that
fact.

Better safe than sorry...

Regards

Jeff
 
Back
Top