Lookup Field Issues

[esn]

I have a lookup field in a table that is "text" data type and
restricted to values in the list. It works properly when entering
data, meaning it won't let the enterer remove the focus from the field
if the text they've entered isn't in the list. But for some reason it
will allow text values to be pasted into the field even if they aren't
in the list. I want to use the field to check for consistency in
preexisting data using copy and paste. In my experience this should
create a "Paste Errors" table with all the records having a value for
this field isn't in the lookup list. Any ideas why I'm not getting
paste errors in this case?

 

[Jeff Boyce]

Are you saying that your table has a field that is of type "lookup"? If so,
check through this newsgroup to get a sense of why folks here hold "lookup
fields" (in tables) in low regard!

Second point ... if you/your users are working directly in the tables, stop
now! Access is a relational database. Access tables store data. Access
FORMS display data. Even though they look like spreadsheets, Access tables
ARE NOT SPREADSHEETS!

Finally, if you use a form and combobox to do that lookup, you can set the
LimitToList property to Yes, ensuring that only valid values are entered.

Good luck!


Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

I have a lookup field in a table that is "text" data type and
restricted to values in the list.

If it's a lookup field then it just *appears* to contain text. It doesn't.

It contains a concealed numeric ID value. That simple fact is concealed from
your view by the infuriating, misdesigned, misleading, and inefficient
so-called "Lookup Wizard" misfeature. (Do you get the impression I don't like
lookup fields? You're right).

It's not just me: see

http://www.mvps.org/access/lookupfields.htm

for a critique.

It works properly when entering
data, meaning it won't let the enterer remove the focus from the field
if the text they've entered isn't in the list. But for some reason it
will allow text values to be pasted into the field even if they aren't
in the list.

Yep. One of the many, many limitations of using lookup fields in tables.

I want to use the field to check for consistency in
preexisting data using copy and paste. In my experience this should
create a "Paste Errors" table with all the records having a value for
this field isn't in the lookup list. Any ideas why I'm not getting
paste errors in this case?

Copy and paste should not be needed. Use a Form (not your table datasheet!!)
for data entry, and leave the default Autocomplete set on the combo box on the
form. The user would need to type only the first few characters of the text
value; Access will jump right to the desired record and insert the numeric
value corresponding to it.

 

[esn]

I have to say those are pretty condescending responses. I understand
the value of forms for data entry and that really doesn't address my
issue. Since I've been involved with this project all data entry has
utilized forms in Access, but there is a mountain of preexisting data
to deal with. The data is already entered and was imported from
various sources - some of it was entered in excel and some of it was
in a preexisting database that was quite a mess. Needless to say,
this data is rife with errors thanks to my predecessors NOT grasping
the value of forms. So I can A: import the data using an import
function, which will exclude all records that don't fit my rules and
leave no simple way of knowing which records were excluded, B: use an
append query that will do exactly the same thing, or C: copy and paste
the records so that I get a paste errors table, which I can then
correct as needed.

As for the primer on lookup fields, the info you've provided (If it's
a lookup field then it just *appears* to contain text. It doesn't.)is
incorrect. If the primary key of the row source is a text field, the
data is stored as text. This is beneficial in my case because queries
written on the data table can use the actual text as criteria without
requiring a join to the lookup table. Since I'm building this
database for multiple users with various levels of Access expertise
this would be preferable to training them all in the finer points of
multiple table queries (or doing all their work for them). And as far
as critiques of lookup fields in general, if anyone has a better
suggestion for how to limit values in a field to a list of 11,000
allowable values let me know. Beats typing "value1" or "value2" or
"value3" ... 11,000 times in a validation rule. I tend to agree more
with the response posted here:
http://improvingsoftware.com/2009/10/02/blog-response-lookup-fields-in-access-are-evil/
than with most of the arguements against lookup fields in the link
you've provided.

It seems to me I've found a glitch in Access that there is no way to
correct. If the primary key referenced in a lookup field has a text
data type, the database will not check values against the lookup table
when records are pasted (even if limit to list is set to yes). I
tried everything I could think of to no avail, and eventually had to
return the primary key to the ID field to get the behavior I'm looking
for. Which means I get to do everyone's work for them.

 

[Gina Whipp]

Esn,

Using look-up fields has the problem of not being to Limit to List but if
you append all the unique values to a table and add that table as the
RowSource of your field the Limit to List will work on a form. I do not
know if this technique will work in a table because I do not use look-up
fields in a table.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Keith Wilby]

I have to say those are pretty condescending responses.

So your debut thread contains an insult to the folks trying to help you?
Responders can only guess your level of experience from your OP and yours
sounds like that of a clueless newbie, so you got appropriate responses IMO,
but I doubt that insults will get you much more in the way of help. You seem
hell-bent on doing things wrong, like using lookup fields and allowing
end-users to build their own queries which can only add to the reasons to
let you get on with it.

 

[Keith Wilby]

A good portion of my development time is spent
making the interface as intuitive as is possible. But that's me, and may
not
be the approach others would take.

It certainly *is* the approach that I take and IMO is the *only* approach.

 

[esn]

I'm not trying to insult anyone who's trying to help me, but I don't
feel like anyone but Gina is actually making any effort to help. Any
post that mentions "data entry" or "forms" is ignoring my situation.
No one here has actually suggested an alternative to my methods that
would work given my circumstances, unless they are really implying
that I should re-renter 3 years worth of data from 3 different
projects using forms. Maybe I'm confused on the use of forms for
importing preexisting data and that's my issue. Would it even be
possible to use forms to do what I'm trying to do? Meaning import
data from various files in various formats? As for commenting on my
"end-users" etc., I know them (and my situation) a lot better than you
do. It's not possible for them to do their jobs without writing
queries and it wouldn't be possible for me to write every possible
query they might need for them. I'm working in a completely different
field than most of the people who use Access, and juggling several
different databases from several different projects. Making them as
intuitive as possible is indeed high on my list of priorities, which
is why I'm using a lookup field in the first place. The databases
I've designed for my own use don't have a single lookup field, but my
boss thinks they're the bees knees and doesn't want to stare at a
bunch of foreign keys that don't mean anything to her. You can take
it up with her if you think that makes her an idiot. For now I need a
way to import preexisting data and data from other sources that will
ensure that it complies with the data integrity rules I've set up.
Using a lookup field is the simplest way I know of to do that. I
could also import the data into new tables and use subselect queries
to compare the values in the imported table to the allowable ones, but
that leaves me with no simple way of correcting the errors, and I
still need to copy and paste or append the data to the existing table
when it's corrected. What advantages would that really have in my
situation? And how could that possibly be more intuitive?

I'm here looking for help with MY SITUATION, not to be your soapbox.
I don't need a lecture but a couple suggestions would be nice. And
don't worry, when all the data is finally brought together and I know
it's accurate, I'll work on convincing my boss to let me convert that
lookup to a simple foreign key, and the comboboxes I have in my forms
will do their job. Getting to that point is my issue.

 

[Jeff Boyce]

No insult intended.

.... but if you didn't want the folks here to use their brains and offer
suggestions based on their experience, just say so.

Sometimes folks just want to be told "what button to push" ... let us know.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

I'm not trying to insult anyone who's trying to help me, but I don't
feel like anyone but Gina is actually making any effort to help. Any
post that mentions "data entry" or "forms" is ignoring my situation.

Ok, I'm really confused now. In your original post you say, to quote:

It works properly when entering
data, meaning it won't let the enterer remove the focus from the field
if the text they've entered isn't in the list. But for some reason it
will allow text values to be pasted into the field even if they aren't
in the list.

So it would seem that you ARE doing data entry - by copying and pasting -
directly into a table.

Tables aren't designed for data entry; lookup fields aren't designed to do
what you're asking them to do.

No one here has actually suggested an alternative to my methods that
would work given my circumstances, unless they are really implying
that I should re-renter 3 years worth of data from 3 different
projects using forms.

Certainly not. A form is just a tool, a window to enable manual interaction
with the data.

Maybe I'm confused on the use of forms for
importing preexisting data and that's my issue. Would it even be
possible to use forms to do what I'm trying to do? Meaning import
data from various files in various formats?

Importing should probably be done using File... Get External Data... Link to
link to the external file, and then running an Append query to migrate the
data into your table. If you have Lookup Fields in your table, then the table
in fact contains a numeric ID, not the text; if you do not have that numeric
value in the external file that you're attempting to import, your task becomes
more complex - you'll need to join to the lookup table to ascertain the
numeric value, if it exists, and (perhaps) to create new records in the lookup
table if the incoming data does not match any existing record.

As for commenting on my
"end-users" etc., I know them (and my situation) a lot better than you
do. It's not possible for them to do their jobs without writing
queries and it wouldn't be possible for me to write every possible
query they might need for them. I'm working in a completely different
field than most of the people who use Access, and juggling several
different databases from several different projects. Making them as
intuitive as possible is indeed high on my list of priorities, which
is why I'm using a lookup field in the first place. The databases
I've designed for my own use don't have a single lookup field, but my
boss thinks they're the bees knees and doesn't want to stare at a
bunch of foreign keys that don't mean anything to her. You can take
it up with her if you think that makes her an idiot.

On the contrary, I think she's perfectly right to want to see the human
meaningful data. That doesn't mean that she should be routinely using *TABLES*
to interact with the data. They're simply not the right tool to do so! Table
datasheets (even with lookup fields and subdatasheets) are very limited in
functionality. If you choose to sacrifice the functionality of forms, then you
are stuck with the limited capabilities of table datasheets.

For now I need a
way to import preexisting data and data from other sources that will
ensure that it complies with the data integrity rules I've set up.
Using a lookup field is the simplest way I know of to do that. I
could also import the data into new tables and use subselect queries
to compare the values in the imported table to the allowable ones, but
that leaves me with no simple way of correcting the errors, and I
still need to copy and paste or append the data to the existing table
when it's corrected. What advantages would that really have in my
situation? And how could that possibly be more intuitive?

You'll need to explain how your current situation is better. Obviously it
isn't, because it lets you append garbage data. If you have garbage data in
your input files, then - at some point - you MUST do the comparison to
allowable entries; this can be automated to a great extent, but there'll still
be cases where the computer CANNOT ascertain the correct value, and a human
interaction will be needed.

I'm here looking for help with MY SITUATION, not to be your soapbox.
I don't need a lecture but a couple suggestions would be nice. And
don't worry, when all the data is finally brought together and I know
it's accurate, I'll work on convincing my boss to let me convert that
lookup to a simple foreign key, and the comboboxes I have in my forms
will do their job. Getting to that point is my issue.

If you could explain a bit more about the nature of the data you're importing
(you have not yet done so), the nature of the tables into which you're
importing (you have not yet done so), the nature of the mismatches (we don't
know that either), we'll be glad to do so.

I apologize of the didactic tone of my previous posts... but given your
intense tone in the message to which I'm replying, I'll wait a bit to
apologize for this one. I'd like to be able to help, but I really do need to
understand the question first.

 

[esn]

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.

 

[Gina Whipp]

Esn,

While I happy for the thanks please understand I dislike look-up fields in
tables as much as the next guy. I just thought since you'd gotten that
information I would offer a way to handle our situation that would eliminate
using a look-up field AND as a by product, give you what you want which is
to Limit the List.

Does that work for you? Because appeneind to a table and making the field
dependent on the newly created table you get what you want with no retyping
or re-entering AND if you have an import routine set up on a form you can
actually append any new values to that table with no one the wiser.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Piet Linden]

FWIW... if the real problem you are dealing with is dirty/imperfect
data, then this is the way I have solved it in the past. Not saying
it's the best way or the only way - only that it worked for me. And
it will do cleaning better and better the more you use it.

The problem I had was that I was trying to summarize cancer treatment
data and people would misspell symptoms and so on. The solution was
to create a bridge table... something like this:

CREATE TABLE SlangTermToMedicalTerm(
SlangTerm TEXT(50) PRIMARY KEY,
MedicalTerm TEXT(50)
)

then at least you can use a simple outer join to identify incorrect
terms and fix them... granted you have lots of variations of
"SlangTerm" per medical term, but as the table grows, your workload
will decrease...

You could use this cross-reference table for updates if you wanted...

Hope I didn't misunderstand the problem...

 

[Jeff Boyce]

Piet

Are you saying that your users were still entering misspellings and slang
terms, but you used your translation table to handle this? And that it
falls to you to figure out which slang terms and misspellings to connect to
which medical terms?

I may be misunderstanding, but wouldn't it also work to provide a list of
medical terms from which the users would have to choose? It seems to me
that this approach would prevent misspellings ...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

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.

Ok, I'll try to post an example that will do so. It will be a crude and
imperfect example because you have chosen not to post any information about
your tables, so I'll make the following assumptions: your master table
tblMaster has a lookup field MyData, actually containing a Long Integer
foreign key MyDataID; and you have a lookup table tblLookup with a Long
Integer primary key MyDataID and a text field MyData.

You're getting input from an external text file (or spreadsheet, the details
will differ but the principle is the same) named MyFile, with nothing
corresponding to MyDataID but containing a text field MyData. This will mostly
match the records in tblLookup but will have errors.

I'll suggest that you create a new table, tblBadLookup, with an autonumber
primary key and all of the other fields in your import file.

You would use File... Get External Data... Link to link to Myfile.

A query

INSERT INTO tblMaster (MyDataID, <fieldname>, <fieldname>, ...)
SELECT tblLookup.MyDataID, MyFile.*
FROM MyFile INNER JOIN tblLookup
ON MyFile.MyData = tblLookup.MyData;

will insert the matching data; a query

INSERT INTO tblBadLookUp (<fieldname>, <fieldname>, ...)
SELECT MyFile.*
FROM MyFile LEFT JOIN tblLookup
ON MyFile.MyData = tblLookup.MyData
WHERE tblLookup.MyData IS NULL;

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.

Hope this helps!

 

[esn]

Thanks for the suggestions

Piet -- you seem to be the only one reading this who has faced the
same issues. Your suggestion is a fantastic one and I wish had done
things that way from the get go. I'm working on the third database
that requires the same sort of error checking process and the same
table would have worked for all three. Next time I end up in a
similar situation I will definitely be creating that cross-reference
table so that I can at least automate the corrections that I've
previously dealt with. I hope someone in the same situation stumbles
across this post some day and avoids some of the headache by following
your advice.

Gina - I think you are trying to help me with creating a list of
allowable values based on data that is already in the database. I
already have the list, so that's not the issue. I need to bounce the
data I'm importing off of the list I already have and track which ones
are rejected so I can correct them.

Jeff - I think you still misunderstand the fact that this data already
exists in some sort of electronic form. Clearly having controls on
what values people can enter during the process of data entry would
avoid this problem altogether, but unfortunately I've inherited a
bunch of files that were entered with little or no restrictions. You
are simply pointing out what someone should have done years ago,
before any of the data was entered, and before I was involved with
this project. I agree completely with what you're saying, but it
doesn't do me much good now. The damage has been done, my job is to
undo it, and re-entering thousands of records isn't really an option.

John - You've hit on exactly what I'm looking to do, but my question
remains this: what is the advantage of doing things that way versus
the method I described? If the only advantage is that I don't need to
have a lookup field in a table that way, then do the disadvantages of
having a lookup field really outweigh all that extra trouble? I guess
that's more of a personal preference question when it comes down to
it, and it's already abundantly clear what most people prefer in this
case. I'll give your method a shot and see how well it works out.

And since everyone seems so curious, the data is wildlife survey data
from three different projects that all have similar but not identical
protocols. More specifically I'm importing vegetation data that
includes a "plant code" field and trying to exclude values that are
incorrect due to a variety of reasons. Some field guides disagree on
the correct scientific name (which is the basis of the code) for a
particular plant for example. People forget exactly what the code is
when writing it down. In previous years the data has been entered
into excel or into poorly designed databases and is therefore full of
typos. Officially recognized names of plants change. All this leads
to the nightmare I'm trying to work out, so that I can actually make
this data consistent and accurate. Therefore I'm comparing every
plant code that I import to a list of plants found in the counties
where the surveys take place. This still isn't really good enough,
since there are still codes that are typos or errors that correspond
to a plant in the list by pure coincidence, but as far as I can tell
it's the best first crack at getting the data looking like it should.
The best first crack, I should say, assuming no one took the time to
design a proper database before the projects began. As so many people
have pointed out, that would really be the best first crack - so maybe
I should stop wasting my time with funky data and start working on a
time machine.

Thanks for your help and suggestions

 

[esn]

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?

 

[John W. Vinson]

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?

I guess I don't understand. What do you WANT to happen when you have invalid
data? Add it to the table anyway?

 

[Keith Wilby]

I have a lookup field in a table that is "text" data type and
restricted to values in the list. It works properly when entering
data, meaning it won't let the enterer remove the focus from the field
if the text they've entered isn't in the list. But for some reason it
will allow text values to be pasted into the field even if they aren't
in the list. I want to use the field to check for consistency in
preexisting data using copy and paste. In my experience this should
create a "Paste Errors" table with all the records having a value for
this field isn't in the lookup list. Any ideas why I'm not getting
paste errors in this case?

OK I'm just going to comment on your OP because I don't have the time to
read the reams of other stuff. If your goal is to correct errors in your
data before importing it then you could use a query with a join on that
field to a lookup *table*. That table would be a single field containing
permitted values. Use your query to return those records that do *not* exist
in the lookup table, correct the errors then run your import routine.

Keith.
www.keithwilby.co.uk

 

[Keith Wilby]

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?

You'd have to repeat the process for every field with suspected bad data.
Trying to do all of the fields in one go is asking for trouble.

Keith.