Re: Lookup fields in tables bad?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Allen; et al,

Thanks so much for the great outline on this subject, and I appreciate very
much your taking the time to help me understand.

I would have replied sooner, but I’ve been down hard with the flu.

In fact I have frequented your site Allen, as well as others posted in this
discussion group trying to gain what knowledge I can from the pros. I’ve
spent hours reading the helpful posts here, and those interesting and helpful
resources you and others make available to guys like me who are not.

And I thank everyone in this group for your generosity, and for your
willingness and your enthusiasm to help others.

To be sure, I have a long, long way to go; but I look forward to reading
more of your writings on this and other topics.

Thanks again for the posts and the help!

Best regards,
Robert


"Allen Browne" wrote:

Okay, lets see if we can clarify things for you here.

In table design, if you choose a field of type "Lookup Wizard", you fire up
a wizard that many of us don't like. We tend to avoid this wizard because of
its side effects. You have probably read this:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

But no one is saying that you should not use foreign keys. Every non-trivial
database has lookup tables -- small tables that contain the valid values for
a field in a table that looks them up. That is the only way to correctly
normalize your data.

There is a debate about whether the lookup table should have a numeric field
as its primary key (in which case your foreign key field will also be a
Number), or whether you should use a natural key (Text type.) If you have a
lookup table for categories, each category name will be unique. If they are
also relatively brief (just a couple of dozen characters), it makes sense to
me to use that as the natural key instead of introducing an artificial key
(such as an AutoNumber.) Sounds like you may have done that in some cases.

Next the question arises as to whether your main table should use a text box
or a combo box for the foreign key field. Combos are great on forms, but
many developers avoid them in the table because it masks what data is really
here. For example, if you use the Lookup Wizard, you get a field of type
Number, but it displays the text. That's confusing for developers and
newbies alike: you actually have to open the table in design view in order
to build a WHERE clause on the field, because you don't know what delimiters
you need just by looking at the data.

You also raise the possibility of using value lists rather than a lookup
table. IMHO, that's the worst possible solution. It's trivial to let the
user add more records to a lookup table, but modifying the items in a lookup
list requires design changes. For more info on this, see:
http://allenbrowne.com/ser-27.html#ValueList

So, FWIW, my recommendations would be:
- Do use foreign keys.
- Avoid the Lookup wizard.
- Use natural keys where appropriate.
- Use combos in your table only rarely.
- A Value List in a combo in a table is too inflexible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert5833" <Robert5833@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:892F69A2-4DE9-4D84-AB48-B7FB2328F482@xxxxxxxxxxxxxxxx
Good day all,

I read recently (having followed some links to various Access related
websites from posts here) that one should avoid altogether the use of
lookup
fields in tables.

Though I am indeed a novice at this, I have created a number of simple
databases over the years, and in my limited understanding thought lookup
fields were one of the benefits of a relational database? So, I created
lots
of lookup fields in my tables...lots and lots of them.

My question is two-fold. First; what is the risk and what bad things can
happen to the wayward soul who unwittingly builds lookup fields into his
tables, and second; if such practice should be avoided like the plague,
how
does one go about eliminating them after the fact?

Maybe one more question; I also use field lists and value lists at the
table
level. Is this the same, and just as bad as having an SQL lookup field in
a
table?

In a copy of a database I’m working on now, I changed all of the table
level
lookup fields (SQL type) to straight text boxes and I’m waiting now for
that
copy to implode…but it hasn’t yet, and all of my forms are working
correctly.
(I have lots and lots of lookups as the Row Source property.)

Since I changed the table fields to Text Boxes, I haven’t tried to enter
any
new data…maybe Armageddon awaits me there?

I rarely enter data directly into my tables; and even when I do it's only
after a first pass at building one, and just before I create a form for
data
entry purposes. Since my forms all have lookup fields in them, will my
house
of cards tumble only if I were to enter data directly into a table?

If anyone has some good insight on this, or would simply to poke fun at me
for my ignorance or even just offer anecdotal comments on what may be my
impending doom, I would appreciate any input at all.

Thank you in advance.

Best regards,
RL



.

Relevant Pages

  • Re: Same database or another?
    ... that I have used the lookup wizard provided. ... if I choose to add tables to database that have absolutely no relation to ... >> work toward the capture of the feral animals. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Ken Snell help!
    ... example I call the People table the "Master table" and Clubs the "Lookup ... where the control lists the members of the given club: ... Code_People (Random alphanumeric field, indexed with no duplicate) ... primary key autonumber and a code field like KP251456. ...
    (microsoft.public.access.formscoding)
  • Re: Lookup fields in tables bad?
    ... My bigger struggles have been with all of the other lookup fields that I ... not sure I understand when a foreign key could be a text box, ... Lists, Field Lists, List Boxes and the like, I’m much clearer now on their ... In table design, if you choose a field of type "Lookup Wizard", you fire up ...
    (microsoft.public.access.tablesdbdesign)
  • Re: VLOOKUP not returning results
    ... re: Access Excel. ... > lists into Access and then have one Excel sheet that looked up any ... >> your lookup range. ... Some of my price lists are over 15,000 items. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Ken Snell help!
    ... I am the author of that sample database, so maybe I can help out here :-) ... example I call the People table the "Master table" and Clubs the "Lookup ... To use the MtoMListHandler, you just need to create on your form five ... where the control lists the members of the given club: ...
    (microsoft.public.access.formscoding)