473,747 Members | 2,556 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Lookup fields - saving space

Say I have a table called tblPeopleInfo, one of the fields in the table is
called FavouriteFruit. The FavouriteFruit field is a lookup field and will
contain Apples, Oranges, Grapes etc..The list can be added to by users of
the database. What is the best way to construct this lookup?

Should it lookup a Table/Query or a Value List?

If it looks up to a Table/Query should the lookup table just have a single
"text" field or should it have an AutoNumber field plus the text field so
that the Lookup field in the original table only looks up an integer thus
saving space?

Thanks,

Paul

Nov 13 '05 #1
1 1900
Use a table for for the lookup values. Most databases have lots of these
little lookup tables in them.

You can use the text value as the primary key: no need for an autonumber.
Although this could use up a few extra bytes, there is really no
justification for using an artificial key value for most of these lookup
tables.

--
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.

"Paul H" <no****@nospam. com> wrote in message
news:Yx******** *********@newsf e6-win.ntli.net...
Say I have a table called tblPeopleInfo, one of the fields in the table is
called FavouriteFruit. The FavouriteFruit field is a lookup field and will
contain Apples, Oranges, Grapes etc..The list can be added to by users of
the database. What is the best way to construct this lookup?

Should it lookup a Table/Query or a Value List?

If it looks up to a Table/Query should the lookup table just have a single
"text" field or should it have an AutoNumber field plus the text field so
that the Lookup field in the original table only looks up an integer thus
saving space?

Thanks,

Paul

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
6407
by: Rob Allen | last post by:
I've got a lookup table for a lookup table and can't work out the SQL to do a search. Can anyone help? As an example, consider the following: Table 1 - books: fields: book_id, price, title, author data: 1, 2.99, 'Life', 'A. Slug' 2, 4.99, 'The Universe', 'A. Planet'
3
2918
by: my-wings | last post by:
I've been reading about how evil Lookup fields in tables are, but I've got to be missing something really basic. I know this subject has been covered before, because I've just spent an hour or two reading about it on google, but there is something I still don't understand, and I'm hoping someone will be willing to explain it to me in small words. Let's say I have a table for addresses, and it includes a field for state. What I would...
1
3362
by: Zachary Turner | last post by:
I want to make a Lookup Field based on another Lookup field. In other words, I have this table A with two fields: ID and Name, where ID is an Autonumber and Name is a friendly name. Then I have a table B which links to A via a Lookup Field so that I can select the friendly name from combo box. Now, I want to have a table C which links to table B via a lookup field, and only displays the items which are selected in some combo box of some...
3
10660
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems that are hard to find. The main problem I am having right now is that I have a report that is sorted by one of these lookup fields and it only displays the record's ID number. When I add the source table to the query it makes several records...
2
2394
by: Lance Williams | last post by:
How can you programatically tell if a field in a table is a lookup field? I am looping through the fields collection and I want to see if any fields are lookup fields. Is there a property, or something taht will tell me if the field is a lookup field. I need to do this at the table level and not at the form level because if a field is a lookup field then I do certain things in the code. BTW, lookup fields suck.
0
1266
by: =?Utf-8?B?RU1hbm5pbmc=?= | last post by:
(I originally posted this to the data access newsgroup but received no replies) I've got an Access 2003 mdb that I'm converting to VB.Net. I'm having trouble with getting the main data source to update after updates to a lookup table. There are 2 tables with a one-to-many relationship in the mdb. VB.Net recognizes this relationship according to it's dataset designer diagram. Currently I have 2 forms: a main form for data-entry and a...
7
2587
by: samdev | last post by:
I have set up a table with a few fields that are set to required....all work fine except the fields that are Lookup from another table. What am I forgetting to set? Thanks!!
2
2112
by: ApexData | last post by:
How do I use a combo-box to do a lookup on my name table “T-NAME”, and then place the values found in the lookup table (ie LNAME, FNAME, MI) into to a row in my existing continuous forms table "T-SICK" ? I will have the combo-box lookup available in each row of my continuous form bound to "T-SICK" to make this happen. Thanks
3
2842
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one dataset to data in a second dataset, using a common key. I will first describe the problem in words and then I will show my code, which has most of the solution done already. I have built an ASP.NET that queries an Index Server and returns a...
0
8978
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9353
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9305
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9222
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8231
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4588
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4860
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2771
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.