Lookups with related tables

[Conor]

I am a new user and I am reading The Missing Manual for Access 2007 by Matthew MacDonald. He recommends the use of lookups fields on data entry forms (pg 162). I have received input from other users recommending against this practice, but for rather vague reasons. I wonder if a couple of moderators or other experts would weigh in on this subject. Thanks in advance.

 

[Banana]

I think a distinction needs to be made...

Lookup Table is a great idea- Lookup Fields is NOT.

The difference is that if you go into table design, select a field, then on the bottom section of the design view, there's two tabs, first tab usually list properties for that field, while second tab is "LookUp" (or was it "Lookup Wizard?"). That one is bad because it creates more problems than it solves, violates normalization and create bloat.

OTOH, a lookup table is just a simple table with a key and value... Let's say we want to catalogue animals by their order- we would want a lookup table listing the order-

Key    Value
1   Mammal
2   Bird
3   Reptile

Then in the table for animals, we refer that key column using relationship between the two tables:

Key   Order   Animal
1   1   Cat
2   1   Dog
3   2   Hawk
4   3   Lizard

That is good because we reduce possibility of data entry errors (entering 'mamal' instead of 'mammal' or 'brid' instead of 'bird') among other things.

HTH.

 

[georgedwilkinson]

I concur. If you re-read the expert advice you've read, you'll probably see that they recommend against lookups at the table level.

 

[missinglinq]

I am a new user and I am reading The Missing Manual for Access 2007 by Matthew MacDonald. He recommends the use of lookups fields on data entry forms (pg 162). I have received input from other users recommending against this practice, but for rather vague reasons. I wonder if a couple of moderators or other experts would weigh in on this subject. Thanks in advance.

Oranges and apples are both round and approximately the same size, but I think you're getting them confused! Lookup fields defined in tables are frowned upon by experienced developers, for a number of reasons. Comboboxes in forms, which are sometimes called "lookup fields" are both safe and handy.

Here's a link to an excellent article on the subject that gives other links as well:

http://www.xtremevbtalk.com/showthread.php?p=758848

 

[Conor]

Thank you, experts. The votes are in. Although I am missing the experience to assess some of the answers, its a unanimous decision - no lookup fields in DB tables. Now let me see if I can figure out how to mark this post as "solved".

Again, many thanks for useful guidance.

Conor

 

[Banana]

Conor, this 'Solved' feature is only available in General forum, not in other subforum for some odd reasons... Sorry. (Confused me as well when I wanted to do the same thing myself)

 

[Conor]

Thanks, Banana. If you like easy (?) challenges, how about looking at my post about 3 or 4 down the list about two team names?