Lookup fields - Yes or No?

[Ann Scharpf]

I am a little confused about the perils of lookup fields vs. the need to
normalize data. I have two tables in my database. Pertinent fields are:

ApproversList
ApproverID (Autonum key)
ApproverName

AGoalEmployees
Approver (List Box field; shows both ID & name. STORES ApproverID)

I thought this was preferable to typing in Approver's name for every
employee. I just select the approver's number (1 - 14) without any risk of
typos causing data mismatches. I am seeing contradictory advice in the
newsgroups.

This one says it is ok to use lookups as long as you don't use a wizard and
control the field yourself:

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

But several posts include links to this rule to NEVER use lookups in tables:

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

Can someone please clarify for me if this is an ok thing to do?

My next question is about data compatibility. When I try to do a query to
link the approver table and the employee table (so I can show the approver's
NAME) I get a data mismatch. The ApproverID is number and I thought that
when I created a lookup field to use it, the data type would automatically be
set up to match. However the field in the employee table is TEXT. I tried
to create an Approver2 field to repeat the process and see where I'd messed
up, but the lookup dialog box does not seem to allow me to create the field
as numeric. Is there a way around this? I guess I could remove the Autonum
ID field in the Approver table and create an indexed field that does not
allow duplicates and create the values myself but it seems like there should
be a more direct method.

Thanks for any advice you can give me on these points.

 

[Ann Scharpf]

I didn't realize that my link to a post on this newsgroup would just come up
with my search results list. Here is the text of Tim Ferguson's post that
says it is ok to use lookups:

There is some misunderstanding about lookup tables and lookup fields.

Look up tables are what you seem to have lots of, and there is nothing
wrong with that. You might have a table looking like

ConditionDescriptions
ID Description
== -----------
1 Knackered
2 Acceptable
3 Smart
4 As New
99 Unseen

and then a numeric field in another table storing the 1s or 2s etc
instead of the words. No problem: you have total control over the
contents of the fields at very little cost in terms of complexity or
performance.

What is really, really, really bad is the Access Wizard for creating look
up tables, because it gets the process hopelessly badly mixed up with the
table design and forms design and GKWhat. It ends up lying to you about
what is in the table -- everyone thinks it's the text, but it's actually
the number, so that it never finds what you are looking for in a query.
It also disguises just how profligate it is with indexes, so you can hit
the 32 indexes per table limit without meaning to.

Better by far to create the tables and relationships by hand (takes all
of twentyfive seconds) and then you know what is going on. As far as UI
implementation goes, it's easy to plug the foreign table into a combo box
or list box (the combo box wizard is fine for this).

The other advantage of creating your own tables is that you can extend
their use vis:

ConditionDescriptions
ID Description VendorDiscount
== ----------- --------------
1 Knackered 33%
2 Acceptable 12%
3 Smart 5%
4 As New 0
99 Unseen 2%


and so on.


Hope that helps
All the best


Tim F

 

[Ann Scharpf]

Thanks for responding but actually the second part of your answser does not
make sense to me. I thought that was exactly what I said. Use the lookup
field in the table and link in forms/queries/reports to get the description.
How does this show that the lookup is bad?

So, you're telling me it is preferable to enter the entire approver's name
in every employee record? How do I ensure that ONLY valid approvers' names
can be entered and that they are entered consistently?

Thanks.

Ann

 

[Larry Daugherty]

Hi Ann,

Lookup Fields in tables = NEVER++

What I believe Tim meant is to use the lookup fields in tables and
that it only takes about 25 seconds to get the information you want in
a Form later.

HTH

 

[Jeff Boyce]

Ann

Your table needs to store the ApproverID, not the Approver name, which can
be looked up.

Tables store data, forms display data. If you want to see the Approver
name, do so in a form, not by working directly in the table.

If you add a combo box to your form, and you base that combo box on the
table that holds ApproverID, ApproverName, you set the bound column to 1,
and the width of the first column to zero. This displays the ApproverName
in the combo box, but stores the ApproverID in your (first) table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mscertified]

You are getting confused about lookup tables/fields versus tables in a
relationship. You relate tables in the 'table relationships' window. NEVER
EVER use the Access wizard to do this because it inserts lookup data in the
table designer which you definitely do not want.

Dorian

 

[Ann Scharpf]

That is basically what I did do. Except I used a list box and I retained the
display of the first column.

Is this an ok thing to do? If yes, how do I get the data types to match?
(As I mentioned in original post, the ApproverID was Autonum but the combo
box field was inserted into the table as text.)

If not, how can I delete the lookup column? I keep getting an error that
it's part of a relationship. But I have established no relationships in the
Relationship Window and I have opened every query and none of them use this
field as a link.

 

[Ann Scharpf]

Jeff:

Upon re-reading, I realize that I missed the KEY word in you post ... FORM.
I have been working directly in the data table, since I am the only person
who works in this database. I can see that I could use a lookup in a form
and insert the ApproverID number into the employee table.

I see the error of my ways and I am trying to delete the original lookup
field from the Employee table. But I keep getting an error (as I just
mentioned in previous post) about the field being part of a relationship.
Can you tell me how I can figure out what other object is linked to this
field so I can break that link and remove the lookup field?

 

[John Vinson]

I see the error of my ways and I am trying to delete the original lookup
field from the Employee table. But I keep getting an error (as I just
mentioned in previous post) about the field being part of a relationship.
Can you tell me how I can figure out what other object is linked to this
field so I can break that link and remove the lookup field?

You don't need to (or want to) *delete the field*. Instead, leave the
field intact, and just turn off its Lookup misfeature.

To do so, open the Table in design view and select the field. Click
the Lookup tab on the lower left corner of the design page. Change the
lookup property from "Combo Box" to "Textbox".

This will set the table to display its actual contents - the ID -
rather than concealing it behind a lookup.

John W. Vinson[MVP]

 

[Ann Scharpf]

Part of what is confusing me is that "everyone" keeps saying the contents of
the field are hidden. I have ALWAYS seen the numeric ID value, not the name.
So I guess I'm not clear on why this is going to work so differently than
what I had. I have made the change but I wish I could understand the
difference. It's hard to remember what you don't understand but just
memorized at some point.

Thanks for helping me.

 

[Larry Daugherty]

Please forgive my stupid keyboad.

Where I improperly wrote that Tim meant "to use" it should have
been "not to use".

 

[Ann Scharpf]

Ah, then that makes sense. Thanks.

 

[Tim Ferguson]

What I believe Tim meant is to use the lookup fields in tables and
that it only takes about 25 seconds to get the information you want in
a Form later.

I think what I meant is that it takes about 25 seconds to set up the table
and its relationship... which is not a great deal of work compared to using
the Wizard.

Returning information from a query based on a master-child query should be
close-on instantaneous and certainly no slower than the mess that the
Wizard creates.

PS -- I never realised how sobering it is to have one's own posts quoted
back. I think that post still stands up...


Tim F