By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

When use a list, and when a lookup table

P: n/a
Hi,

When should I use a list (in table properties: like
Ford;Mercedes;BMW;Audi )
and when should I use a lookup table??

And second question: IF I use a lookup table, should I always make a
relation (1-to-many) in my relation scheme?

Any help is greatly appreciated, I'm a bit puzzled.

thx
Paul
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Paul wrote:
Hi,

When should I use a list (in table properties: like
Ford;Mercedes;BMW;Audi )
and when should I use a lookup table??

And second question: IF I use a lookup table, should I always make a
relation (1-to-many) in my relation scheme?

Any help is greatly appreciated, I'm a bit puzzled.

thx
Paul

1) Never do either in table properties. Lookups belong in forms/reports
only.

2) Use a table whenever the list has a good chance of changing.

3) Create the relationship

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2

P: n/a
"Rick Brandt" <ri*********@hotmail.com> wrote in news:GA6ae.1046$yd7.694
@newssvr11.news.prodigy.com:
Paul wrote:
Hi,

When should I use a list (in table properties: like
Ford;Mercedes;BMW;Audi )
and when should I use a lookup table??


1) Never do either in table properties. Lookups belong in forms/reports
only.

Hi, what is the reason for not using a lookup in table properties?
I noticed this advice earlier, but I never understood why exactly.

Cheers.

K.
Nov 13 '05 #3

P: n/a
there's info on this here:
http://www.mvps.org/access/tencommandments.htm

If you've ever tried to debug or understand someone's database who used
them, you'd understand. I had that happen to me once. All the "hard"
stuff was easy, but this totally screwed me up. Table data should be
painfully obvious, with absolutely no other "stuff" going on behind the
scenes. And that's exactly what table-level lookups are. Yes, they
might save you a step or two, but maybe it's just me. When I'm doing
the development, _I_ like to have control over what's happening and not
have Access do it for me.

In a nutshell, have someone you know slap up a database with lookups in
it, and then ask you to do something simple and see how hard the
lookups make it. Trivial stuff should take minutes, and not be
obfuscated by lookup crap.

just my opinion... but try it sometime and see for yourself.

Nov 13 '05 #4

P: n/a
On 22 Apr 2005 05:59:21 -0700, al********@it4us.nl (Paul) wrote:
Hi,

When should I use a list (in table properties: like
Ford;Mercedes;BMW;Audi )
and when should I use a lookup table??

And second question: IF I use a lookup table, should I always make a
relation (1-to-many) in my relation scheme?

Any help is greatly appreciated, I'm a bit puzzled.

thx
Paul


Generally speaking, lists should be kept in tables, and lookup items should be
identified by a surrogate key (usually an Autonumber), not by a name.

Let's take your example above of automobile brands, and say there's a table of
automobiles, each one having a brand as an attribute. Ler's also say you
used a value list combo box for the selection like Ford;Mercedes;BMW;Audi.

Now, let's say you've decided you need this data to be editable in more than
one form, so you create the new form and copy the combo box from the first
form to the second form. You now have 2 combo boxes with the same list.

Next, let's say 2 months later, you decide you want to add Fiat to the list,
and you also want to change the name of Merceded to Mercedes Benz. So - you
track down the first form, but you forget about the second form. Now, the
second form won't allow you to pick Fiat, and any Mercedes Benz selected in
the first form won't be the same as the Mercedes items selected in the second
form.

Additionally, what happens when you decide you need to sort cars by something
like manufacturer's home country? Now, you'll have to have an auto brand
table, so there's somewhere to store the country.

So, now let's say, based on the discussion above, we agree that we want to use
a table for auto brands, but we use the name as the identifier, not a
surrogate key.

The first consequence of this is that the database is larger than it needs to
be because the entire brand name is duplicated everywhere it is used, rather
than the mere 4-bytes it would take to store a numeric ID. The next
consequence is that we can't safely rename a brand name unless we also change
it everywhere it's used. We can use a cascade-updating relationship to have
Access fix that up for us, but if there are a lot of records affected, this
can be a massive update that could mess up other users trying to enter or edit
data in a multi-user system, and there's would still be the issue of the
wasted space to store the duplicate name information everywhere.

What we're really talking about here is database normalization theory. If you
want a deeper understanding, find a good book on relational database design.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.