473,290 Members | 1,897 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,290 software developers and data experts.

When use a list, and when a lookup table

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
4 2037
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
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Chuck Van Den Corput | last post by:
I have an application with one main table with a jillion attributes. There are numerous other tables, but these are either children of the main table or lookup tables. I am finding that I am...
5
by: Marie | last post by:
Access97 I have a table containing addresses with a separate field for State. Is there a way to create a query that returns an unique list of the states in that table and still be updateable? I...
2
by: Emily Jones | last post by:
That subject line will have Joe Celko apoplectic!! Let's say I have this thing. Oh, I don't know, a car. No, a vehicle. Can we agree that is an entity type? Some attributes might be VIN, Fuel,...
1
by: Dave | last post by:
I have a problem which I know there is an answer to but have so far been unsuccessful in finding it. It is quite simple in theory. I have created a form which uses a table for lookup values and...
0
by: gerry | last post by:
I want to populating an asp list box from a simple access lookup list (single column not a table.)I don't want to create tables just for lookups as the values will be descriptive only and will...
3
by: dbuchanan | last post by:
Hello, (Windows forms - SQL Server) I fill my datagrid with a stored procedure that includes relationships to lookup tables so that users can see the values of the combobox selections rather...
3
by: Patrick [MSFT] | last post by:
Let me preface this with the goal I'm trying to achieve is mimic a feature of another language (Dexterity used by Microsoft Dynamics) and so while a filling a drop down list is a workable solution...
5
by: sensreview | last post by:
Hello, I need help in selecting a value from combo list thru one lookup table and update different table on MS access form. For eg; I have a lookup table of usernames, I need to use this...
2
by: Lysander | last post by:
I have not seen this feature documented before, so I thought I would share it with you, as I will be using it in a later article. For a combo or list box, the source data is normally a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.