A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup Field Issues



 
 
Thread Tools Display Modes
  #1  
Old January 13th, 2010, 08:12 PM posted to microsoft.public.access.tablesdbdesign
esn
external usenet poster
 
Posts: 34
Default Lookup Field Issues

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?
  #2  
Old January 13th, 2010, 10:49 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup Field Issues

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.

"esn" wrote in message
...
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?



  #3  
Old January 14th, 2010, 02:06 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Lookup Field Issues

On Wed, 13 Jan 2010 11:12:26 -0800 (PST), esn wrote:

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.
--

John W. Vinson [MVP]
  #4  
Old January 14th, 2010, 03:55 AM posted to microsoft.public.access.tablesdbdesign
esn
external usenet poster
 
Posts: 34
Default Lookup Field Issues

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 he
http://improvingsoftware.com/2009/10...cess-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.
  #5  
Old January 14th, 2010, 04:06 AM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Lookup Field Issues

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

"esn" wrote in message
...
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 he
http://improvingsoftware.com/2009/10...cess-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.



  #6  
Old January 14th, 2010, 02:43 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Lookup Field Issues

If you want to talk about condescending, that blog post beats anything I've
seen in a while. The person writing it admits he doesn't use Access much,
then dismisses every objection to lookup fields as irrelevant without
investigating any of the claims, or by glossing over what he seems to
acknowledge are real objections, or by acknowledging he does not understand
the objection but that it is irrelevant anyhow.

I use Limit To List to restrict allowable values in a combo box on a form.
No validation rule is needed. I regard tables as places to store data, not
as substitute forms. If I need a combo box, I build it on the form, not in
the table. Preexisting data can be a problem, but sometimes there's no
solution but to import it all and fix it, or fix it before importing if that
makes more sense.

It seems I approach database design differently than you do. I typically
build databases in support of the jobs other people need to do. I don't need
to know all the details of their jobs to build a database, and they should
not need to know much about databases to do their jobs. If I am asking low-
level users to build queries or otherwise get under the hood then I am not
doing my job as developer. 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.

esn wrote:
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 he
http://improvingsoftware.com/2009/10...cess-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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1

  #7  
Old January 14th, 2010, 03:35 PM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Lookup Field Issues

"esn" wrote in message
...
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.

  #8  
Old January 14th, 2010, 05:08 PM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Lookup Field Issues

"BruceM via AccessMonster.com" u54429@uwe wrote in message
news:a2199bb83543f@uwe...
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.

  #9  
Old January 14th, 2010, 06:39 PM posted to microsoft.public.access.tablesdbdesign
esn
external usenet poster
 
Posts: 34
Default Lookup Field Issues

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.
  #10  
Old January 14th, 2010, 07:02 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup Field Issues

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.

"esn" wrote in message
...
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 he
http://improvingsoftware.com/2009/10...cess-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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.