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
  #21  
Old January 15th, 2010, 06:00 AM posted to microsoft.public.access.tablesdbdesign
esn
external usenet poster
 
Posts: 34
Default Lookup Field Issues

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?
  #22  
Old January 15th, 2010, 07:06 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Lookup Field Issues

On Thu, 14 Jan 2010 21:00:53 -0800 (PST), esn wrote:

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?

--

John W. Vinson [MVP]
  #23  
Old January 15th, 2010, 10:59 AM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Lookup Field Issues

"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?


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

  #24  
Old January 15th, 2010, 11:23 AM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Lookup Field Issues

"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?


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.

  #25  
Old January 15th, 2010, 11:24 AM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Lookup Field Issues

"John W. Vinson" wrote in message
...

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.



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

Keith.

  #26  
Old January 15th, 2010, 01:46 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Lookup Field Issues

2b. Nor will a query that uses a company name in that field as a criteria.

Rebuttal: When writing queries, you do have to specify the criteria using the
underlying value rather than the user-friendly displayed value. However, you
have to assume that someone writing queries is not the target of the end-user
abstraction provided by lookups and will know to supply a numeric criteria
for “CompanyID.”

Having to use an invisible underlying value for a critieria has the potential
to be a real problem. If I want to look up a company by name, or if I want
to do a Like criteria, the lookup field seems to offer no help. In any case,
the OP spoke of end users constructing their own queries.

You also wrote "Wrong, Wrong, Wrong" at the idea of other databases not being
able to understand the lookup fields. In my test, exporting the lookup field
(to Excel, in my test) exported only the bound column. The Company name (to
use the current example) is not exported. This could prove inconvenient.

The article you attacked referred to the "user", which you took to mean the
end use only. In context it would seem to be any person working with the
database.

In your demonstration early in the blog post about the use of a lookup field
you showed the table being used directly for data entry. I would not expose
the table to the end users, as there is too much chance for them to do damage
to the data. I (and most developers) use forms for data entry, in which case
I would build by own combo box there, not in the table. You apparently would
send the users directly to the table for data entry, which would seem to
indicate you regard the practice as acceptable. The blog posting would have
been helped had you started out by saying so. If I misunderstand, what is
the point of building the combo box in the table rather than on the form,
other than that it is automatically a combo box when you drag it from the
field list to the form, or use autoform? If you regard the objections to
lookup fields as utterly without merit, do you go so far as to advocate their
use whenever possible?

JohnFx wrote:
Minor correction. I never said that I don't use Access much. I said "...don’t
do nearly as much Access development as I once did.."

More specifically, for a period of about 10 years I used it daily as one of
my primary tools. Recently I have been down to only about 5 hours a week
with it. I've been using Access since version 1.0 professionally and have
written countless apps in it and trained dozens of people how to use it over
those years.

I'd also like to know which "real objection" you think I glossed over. I
didn't find any of them to be valid and didn't think I implied otherwise.

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,

[quoted text clipped - 24 lines]
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 http://www.accessmonster.com

  #27  
Old January 17th, 2010, 04:02 AM posted to microsoft.public.access.tablesdbdesign
Dale Fye
external usenet poster
 
Posts: 2,651
Default Lookup Field Issues

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?



  #28  
Old January 17th, 2010, 04:05 AM posted to microsoft.public.access.tablesdbdesign
Dale Fye
external usenet poster
 
Posts: 2,651
Default Lookup Field Issues

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

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





  #29  
Old January 19th, 2010, 05:03 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup Field Issues

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

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

"Jeff Boyce" wrote in message
...
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 02:14 PM.


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