Didn't intend the "intense tone" mentioned, just frustrated with the
slew of responses recommending a combo box on a form, which clearly is
not a solution to my particular problem. You guys know a lot more
about this stuff than I do and I'm not trying to claim otherwise, but
that doesn't mean I'm a complete idiot either. Thanks for putting in
a little more effort to understanding the problem.
So it would seem that you ARE doing data entry - by copying and pasting -
directly into a table.
I guess I'm considering the copy and paste process "importing." What
I'm trying to do is import data that has already been entered. If my
terminology there is screwed up I apologize, but I feel like that's
very different objective from "entering." Any time I have referenced
"data entry" I'm referring to punching in the records one by one.
The suggestion about using "File... Get External Data... Link to" hits
closer to the problem, but as I wrote above combining this feature
with an append query results in an message containing the number of
records that were not imported due to violations, with no simnple way
of determining which records those are. If I then want to determine
what needs to be corrected I have to use another query to compare the
external data to the imported data, which will show unmatched records
as results but doesn't allow the corrections to be made within those
results. Therefore I need to go back and find the records in the
source data separately, correct them, and then repeat the append query
with only the unmatched records. This ends up being highly
inefficient given the number of records and errors I'm working with.
I'm importing a few to 10 thousand records at a time and the number of
records with errors is somewhere around ten percent. Some of the
erroneous values are repeated many times in the data, but others only
appear a few times. The errors have little consistency and arise in
various ways. In other words, if I were to list unique incorrect
values there would be at least a few hundred. I don't have the time
or patience to automate all of these corrections in an update query or
use the Replace function a few hundred times to fix the source data.
The chances of my own mistakes creeping into the data at that point
also seem unacceptably high.
What I find to be an easier method is to copy and past the data
directly into the data tables from the source files, either
spreadsheets or existing tables. This way any records that comply
with all of my rules are pasted and those that don't are pasted to a
separate table. I can use this table to make all the corrections I
need by hand, then paste the records from "Paste Errors" into the data
table. This, while clearly looked down upon, ends up being a much
faster and more reliable way to get the records corrected and appended
to the table in my experience. I understand that this is far from an
ideal process, but having a bunch of unorganized data from various
sources is far from an ideal situation.
The purpose of the lookup field in this process I think is obvious.
There are other ways I could work around using a lookup field but as
far as I understand it they would be more complex and not particularly
advantageous. An example would be to paste all of the data to a new
table before correcting it, then relate the values in the Lookup table
to the values in the new table. From there I can find unmatched, but
the "unmatched records" results aren't going to be upadateable, so I
will need to look up the records in the source data independently
based on the results of that query, then correct them. In essence I'm
using the lookup feild as a shortcut for this process, which will
isolate the records with values that don't match and allow me to
correct them immediately without things being overly complex.
The original question, then, is why does this work if the primary key
of the lookup table is an ID field, but not if the primary key is
text? It was suggested to me by someone in a different group that
things would be greatly simplified if I did away with the ID field in
the lookup table, because the corresponding text values are required
and unique already. As I stated above this would make my boss happy.
(I know she shouldn't be messing with the data in tables but you try
telling her that and see where it gets you.) Another consideration is
that some of the data I'm importing comes from older databases with
lookup fields based on a text column in a lookup table. Making that
conversion resulted in a breakdown of my copying and pasting workflow,
because incorrect values were no longer rejected by the field, and no
matter what changes I made nothing seemed to help. And strangely
enough, as I originally posted, the field will not except incorrect
values when they are typed in. The only solution I can find is to
move the primary key in the lookup table back to the "ID" field and
tell my boss to deal with it.
As for providing more info on the data and the tables, I understand it
may be hard to grasp my perspective without more info, but at this
point I've invested too much time in this discussion already. If
you're willing to assume I have a decent grasp of database design (and
I know most people here aren't as soon as "lookup" is mentioned) and
that I'm doing the best I can based on the various objectives and end-
users this database has to serve, then I'd appreciate any insight.
